[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT *
FROM qa_plan_chars
WHERE plan_id = p_id AND char_id = e_id;
select 1
from qa_chars qc
where qc.char_id = x_char_id
AND qc.values_exist_flag = 1;
x_lov_sql := 'SELECT short_code, description
FROM qa_char_value_lookups
WHERE char_id = :1 ';
x_lov_sql := 'SELECT short_code, description
FROM qa_plan_char_value_lookups
WHERE plan_id = :1
AND char_id = :2';
x_lov_sql := 'SELECT department_code, description
FROM bom_departments_val_v
WHERE organization_id = :1
ORDER BY department_code';
x_lov_sql := 'SELECT wip_entity_name, description
FROM wip_discrete_jobs_all_v
WHERE organization_id = :1
ORDER BY wip_entity_name';
'select WE.wip_entity_name, WDJ.description
from wip_entities WE, wip_discrete_jobs WDJ
where WDJ.organization_id = :1 and
WDJ.status_type in (3,4) and
WDJ.wip_entity_id = WE.wip_entity_id and
WE.entity_type IN (6, 7)
order by WE.wip_entity_name';
x_lov_sql := 'SELECT line_code, description
FROM wip_lines_val_v
WHERE organization_id = :1
ORDER BY line_code';
x_lov_sql := 'SELECT resource_code, description
FROM bom_resources_val_v
WHERE organization_id = :1
ORDER BY resource_code';
x_lov_sql := 'SELECT vendor_name, segment1
FROM po_vendors
WHERE nvl(end_date_active, sysdate + 1) > sysdate
ORDER BY vendor_name';
x_lov_sql := 'SELECT segment1, vendor_name
FROM po_pos_val_v
ORDER BY po_header_id';
x_lov_sql := 'SELECT customer_name, customer_number
FROM qa_customers_lov_v
WHERE status = ''A''
AND nvl(customer_prospect_code, ''CUSTOMER'') = ''CUSTOMER''
ORDER BY customer_number';
x_lov_sql := 'SELECT order_number, order_type
FROM qa_sales_orders_lov_v mso
ORDER BY order_number';
/*x_lov_sql := 'SELECT to_char(sl.line_number), msik.concatenated_segments
FROM mtl_system_items_kfv msik, so_lines sl
WHERE sl.inventory_item_id = msik.inventory_item_id'; */
x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date) ' ||
'FROM po_releases pr ' ||
'WHERE pr.po_header_id = :1 ' ||
'ORDER BY pr.release_num ';
x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date)
FROM po_releases pr
ORDER BY pr.release_num';
mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
non-pjm enabled orgs).
rkaza, 11/10/2001.
*/
--
-- Bug 5249078. Changed pjm_projects_all_v to
-- pjm_projects_v for MOAC compliance.
-- bso Thu Jun 1 10:46:50 PDT 2006
--
x_lov_sql := 'SELECT project_number, project_name
FROM pjm_projects_v
ORDER BY project_number';
x_lov_sql := 'select task_number, task_name
from mtl_task_v
where project_id = :1
ORDER BY task_number';
x_lov_sql := 'SELECT task_number, task_name
FROM mtl_task_v
ORDER BY task_number';
x_lov_sql := 'SELECT to_char(sh.order_number), sot.name
FROM so_order_types sot,
oe_order_headers sh,
qa_customers_lov_v rc
WHERE sh.order_type_id = sot.order_type_id and
sh.sold_to_org_id = rc.customer_id and
sh.order_category_code in (''RETURN'', ''MIXED'')
ORDER BY sh.order_number';
x_lov_sql := 'select distinct to_char(oel.line_number),''RMA Number: '' ||
sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
x_lov_sql := 'SELECT uom_code, description
FROM mtl_item_uoms_view
WHERE organization_id = :1
AND inventory_item_id = :2
ORDER BY uom_code';
x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
FROM mtl_item_revisions
WHERE inventory_item_id = :1
AND organization_id = :2
ORDER BY revision';
x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
FROM mtl_item_revisions mir,
mtl_system_items_kfv msi
WHERE mir.inventory_item_id = msi.inventory_item_id
AND mir.organization_id = msi.organization_id
AND msi.revision_qty_control_code = 2
AND mir.inventory_item_id = :1
AND mir.organization_id = :2
ORDER BY revision';
x_lov_sql := 'SELECT secondary_inventory_name, description
FROM mtl_secondary_inventories
WHERE organization_id = :1
AND nvl(disable_date, sysdate+1) > sysdate ';
x_lov_sql := 'select lot_number, description
from mtl_lot_numbers
where organization_id = :1' ||
' and inventory_item_id = :2 ' ||
' and (disable_flag = 2 or disable_flag is null)';
x_lov_sql := 'select lot_number, description
from mtl_lot_numbers
where organization_id = :1 '||
' and inventory_item_id = :2 ' ||
' and (disable_flag = 2 or disable_flag is null)';
x_lov_sql := 'SELECT lot_number, lot_expiration_date
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = ' || x_transaction_id || '
ORDER BY lot_number';
x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
FROM mtl_serial_numbers_all_v msn
WHERE msn.current_organization_id = :1 ' ||
' AND msn.inventory_item_id = :2 ';
x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
FROM mtl_serial_numbers_all_v msn
WHERE msn.current_organization_id = :1 ' ||
' AND msn.inventory_item_id = :2 ';
x_lov_sql := 'SELECT msn.serial_number, msn.current_status
FROM mtl_serial_numbers msn,
mtl_transaction_lots_temp mtlt
WHERE msn.line_mark_id = ' || x_transaction_id || '
AND mtlt.transaction_temp_id = msn.line_mark_id
AND mtlt.serial_transaction_temp_id = msn.lot_line_mark_id
AND mtlt.lot_number = ' || '''' || x_lot_number || '''' || '
AND mtlt.lot_number IS NOT NULL
UNION ALL
SELECT msn.serial_number, msn.current_status
FROM mtl_serial_numbers msn
WHERE msn.line_mark_id = ' || x_transaction_id || '
ORDER BY serial_number';
'SELECT cii.instance_number, cii.instance_description
FROM
csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
WHERE
msib.organization_id = mp.organization_id and
msib.organization_id = cii.last_vld_organization_id and
msib.inventory_item_id = cii.inventory_item_id and
msib.eam_item_type in (1,3) and
msib.serial_number_control_code <> 1 and
sysdate between nvl(cii.active_start_date, sysdate-1)
and nvl(cii.active_end_date, sysdate+1) and
mp.maint_organization_id = :1';
x_lov_sql := 'SELECT
distinct msn.serial_number, msn.descriptive_text
FROM
mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
WHERE
msib.organization_id = mp.organization_id and
msib.organization_id = cii.last_vld_organization_id and
msib.inventory_item_id = cii.inventory_item_id and
msib.eam_item_type in (1,3) and
sysdate between nvl(cii.active_start_date(+), sysdate-1)
and nvl(cii.active_end_date(+), sysdate+1) and
msib.organization_id = msn.current_organization_id and
cii.serial_number=msn.serial_number and
msib.inventory_item_id = msn.inventory_item_id and
mp.maint_organization_id = :1 and
msn.inventory_item_id = :2 and --removed nvl: serial number requires asset group as well
cii.instance_id= nvl(:3, cii.instance_id)
order by msn.serial_number';
x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
FROM wip_operations_all_v
WHERE wip_entity_id = :1
AND organization_id = :2
ORDER BY operation_seq_num';
x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
FROM wip_operations_all_v
WHERE wip_entity_id = :1
AND organization_id = :2
AND repetitive_schedule_id =
(
SELECT repetitive_schedule_id
FROM wip_first_open_schedule_v
WHERE line_id = :3
AND wip_entity_id = :4
AND organization_id = :5
)
ORDER BY operation_seq_num';
x_lov_sql := 'SELECT to_char(line_num), concatenated_segments
FROM po_lines_val_v
WHERE po_header_id = :1
ORDER BY line_num';
x_lov_sql := 'SELECT to_char(shipment_num), shipment_type
FROM po_shipments_all_v
WHERE po_line_id =
(SELECT po_line_id
FROM po_lines_val_v
WHERE line_num = :1
AND po_header_id = :2)';
x_lov_sql := 'SELECT to_char(pll.shipment_num), pll.shipment_type
FROM po_line_locations pll
WHERE pll.ship_to_location_id is not null
AND pll.po_line_id =
(SELECT po_line_id
FROM po_lines
WHERE line_num = :1
AND po_header_id= :2 )';
x_lov_sql := 'SELECT receipt_num, vendor_name
FROM ( SELECT DISTINCT RCVSH.RECEIPT_NUM,
POV.VENDOR_NAME
FROM RCV_SHIPMENT_HEADERS RCVSH,
PO_VENDORS POV,
RCV_TRANSACTIONS RT
WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID)
ORDER BY receipt_num';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1 ' ||
' and inventory_item_id in (
SELECT bic.component_item_id
FROM bom_inventory_components bic,
bom_bill_of_materials bom
WHERE bic.bill_sequence_id = bom.bill_sequence_id AND
bic.effectivity_date <= sysdate AND
nvl(bic.disable_date, sysdate+1) > sysdate AND
bom.assembly_item_id = :2 AND
bom.organization_id = :3)
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE 1 = 2
ORDER BY concatenated_segments';
x_lov_sql := 'select distinct msikfv.concatenated_segments, msikfv.description
from mtl_system_items_b_kfv msikfv, mtl_parameters mp
where msikfv.organization_id = mp.organization_id
and msikfv.eam_item_type in (1,3)
and mp.maint_organization_id = :1
order by msikfv.concatenated_segments';
x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
WHERE msib.organization_id = :1
and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
and msib.inventory_item_id = meaav. maintenance_object_id
and meaav.maintenance_object_type = 2 --non serialized item
ORDER BY meaav.activity';
x_lov_sql := 'SELECT
msib.concatenated_segments activity ,
msib.description activity_description
FROM mtl_eam_asset_activities meaav,
mtl_system_items_b_kfv msib
WHERE msib.organization_id = :1
AND meaav.maintenance_object_id = :2 --pass asset group inventory_item_id
AND (meaav.end_date_active is null
OR meaav.end_date_active > sysdate)
AND (meaav.start_date_active is null
OR meaav.start_date_active < sysdate)
AND msib.inventory_item_id = meaav.asset_activity_id
AND meaav.maintenance_object_type = 2 --non serialized item
ORDER BY msib.concatenated_segments';
x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
WHERE msib.organization_id = :1
and meaav.maintenance_object_id = :2 --pass asset instance_id
and meaav.maintenance_object_type = 3 --serialized item
and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
and msib.inventory_item_id = meaav.inventory_item_id
ORDER BY meaav.activity';
x_lov_sql := 'SELECT
msi.concatenated_segments activity ,
msi.description activity_description
FROM mtl_eam_asset_activities meaa,
mtl_system_items_b_kfv msi
WHERE msi.organization_id = :1
AND meaa.maintenance_object_id = :2 --pass asset instance_id
AND meaa.maintenance_object_type = 3 --serialized item
AND (meaa.end_date_active is null
OR meaa.end_date_active > sysdate)
AND (meaa.start_date_active is null
OR meaa.start_date_active < sysdate)
AND msi.inventory_item_id = meaa.asset_activity_id
ORDER BY msi.concatenated_segments';
x_lov_sql := 'SELECT license_plate_number, attribute1
FROM wms_license_plate_numbers
ORDER BY license_plate_number';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_item_locations_kfv
WHERE organization_id = :1
AND subinventory_code = :2
AND nvl(disable_date, sysdate+1) > sysdate
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT party_name, party_number
FROM hz_parties
WHERE status = ''A''
AND party_type IN (''ORGANIZATION'',''PERSON'')
ORDER BY party_name';
x_lov_sql := 'SELECT cii.instance_number, cii.serial_number
FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
WHERE cii.inventory_item_id = msik.inventory_item_id
AND cii.last_vld_organization_id = msik.organization_id
ORDER BY 1';
x_lov_sql := 'SELECT name, description
FROM csi_counters_vl
WHERE trunc(sysdate) BETWEEN
nvl(start_date_active, trunc(sysdate))
AND nvl(end_date_active, trunc(sysdate))
ORDER BY 1';
x_lov_sql := 'SELECT cc.name, cc.description
FROM cs_counters cc, cs_counter_groups ccg
WHERE cc.counter_group_id = ccg.counter_group_id
AND ccg.template_flag = ''N''
ORDER BY 1';
x_lov_sql := 'SELECT amr.title, amr.version_number
FROM qa_ahl_mr amr
WHERE trunc(sysdate) BETWEEN
trunc(nvl(amr.effective_from, sysdate)) AND trunc(nvl(amr.effective_to, sysdate))
ORDER BY 1';
x_lov_sql := 'SELECT incident_number, summary
FROM cs_incidents
ORDER BY 1';
x_lov_sql:= 'SELECT wip_entity_name, description
FROM wip_discrete_jobs_all_v
WHERE organization_id = :1
ORDER BY wip_entity_name';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT cr.repair_number,
cr.problem_description
FROM csd_repairs cr
WHERE status not in (''C'', ''H'')
ORDER BY 1';
x_lov_sql := 'SELECT task_number, task_name
FROM JTF_TASKS_VL
ORDER BY 1';
x_lov_sql := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC FROM GME_BATCH_HEADER '||
'WHERE ORGANIZATION_ID is null or ORGANIZATION_ID = :1'||
'ORDER BY BATCH_NO';
x_lov_sql := 'SELECT to_char(STEPS.BATCHSTEP_NO) ,OPS.OPRN_DESC BATCHSTEP_DESC '||
'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
'WHERE STEPS.BATCH_ID = :1 ' ||
'AND STEPS.OPRN_ID = OPS.OPRN_ID '||
'ORDER BY BATCHSTEP_NO';
x_lov_sql := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
'FROM GMO_BATCH_STEPS_V '||
'WHERE BATCHSTEP_ID = :1 '||
' AND BATCH_ID = :2 '||
' ORDER BY PROCESS_OPERATION';
x_lov_sql := 'SELECT STEPS.ACTIVITY, ACTIVITIES.ACTIVITY_DESC '||
'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
'WHERE STEPS.BATCHSTEP_ID = :1 '||
' AND STEPS.BATCH_ID = :2 '||
' AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY '||
'ORDER BY ACTIVITY';
x_lov_sql := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
'WHERE GBSR.BATCHSTEP_ACTIVITY_ID = :1 '||
'AND GBSR.BATCHSTEP_ID = :2 '||
'AND GBSR.BATCH_ID = :3 '||
'AND GBSR.RESOURCES = CRMV.RESOURCES '||
'ORDER BY RESOURCES';
x_lov_sql := 'SELECT DISTINCT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
'FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE '||
'WHERE GP.PARAMETER_ID = GE.PARAMETER_ID '||
'AND GE.RESOURCES = :1'||
' ORDER BY PARAMETER_NAME';
x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
FROM wip_operations_all_v
WHERE wip_entity_id = :1
AND organization_id = :2
ORDER BY operation_seq_num';
-- nothing should be selected.
x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
FROM wip_operations_all_v
WHERE 1 = 2' || '
ORDER BY operation_seq_num';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT secondary_inventory_name, description
FROM mtl_secondary_inventories
WHERE organization_id = :1
AND nvl(disable_date, sysdate+1) > sysdate
ORDER BY secondary_inventory_name';
x_lov_sql := 'SELECT concatenated_segments, description
FROM mtl_item_locations_kfv
WHERE organization_id = ' || org_id || '
ORDER BY concatenated_segments';
x_lov_sql := 'SELECT status_code,description
FROM mtl_material_statuses
ORDER BY status_code';
x_lov_sql := 'SELECT status_code,description
FROM mtl_material_statuses
ORDER BY status_code';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT k_number, short_description
FROM oke_k_headers_lov_v order by k_number';
x_lov_sql := 'SELECT line_number, line_description
FROM oke_k_lines_full_v order by line_number';
x_lov_sql := 'SELECT deliverable_num, description
FROM oke_k_deliverables_vl order by deliverable_num' ;
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';
x_lov_sql := 'SELECT v.short_code code,
v.description
FROM qa_char_value_lookups v
WHERE v.char_id = :1
ORDER BY 1';