[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT plan_id,
char_id,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
default_value_id,
result_column_name,
values_exist_flag,
displayed_flag,
decimal_precision,
uom_code,
read_only_flag,
ss_poplist_flag,
information_flag
FROM qa_plan_chars
WHERE plan_id = p_id
AND enabled_flag = 1;
SELECT QSC.spec_id,
QSC.char_id,
QSC.enabled_flag,
QSC.target_value,
QSC.upper_spec_limit,
QSC.lower_spec_limit,
QSC.upper_reasonable_limit,
QSC.lower_reasonable_limit,
QSC.upper_user_defined_limit,
QSC.lower_user_defined_limit,
QSC.uom_code
FROM qa_spec_chars QSC,
qa_specs QS
WHERE QSC.char_id = e_id
AND QSC.spec_id = QS.common_spec_id
AND QS.spec_id = s_id;
SELECT QSC.spec_id,
QSC.char_id,
QSC.enabled_flag,
QSC.target_value,
QSC.upper_spec_limit,
QSC.lower_spec_limit,
QSC.upper_reasonable_limit,
QSC.lower_reasonable_limit,
QSC.upper_user_defined_limit,
QSC.lower_user_defined_limit,
QSC.uom_code
FROM qa_spec_chars QSC,
qa_specs QS
WHERE QSC.spec_id = QS.common_spec_id
AND QS.spec_id = s_id;
SELECT plan_id,
char_id,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
default_value_id,
result_column_name,
values_exist_flag,
displayed_flag,
decimal_precision,
uom_code,
read_only_flag,
ss_poplist_flag,
information_flag
FROM qa_plan_chars
WHERE plan_id = p_id
AND char_id = e_id
AND enabled_flag = 1;
select uom_code
from qa_spec_chars
where spec_id = p_spec_id
and char_id = p_element_id;
select uom_code
from qa_chars
where char_id = p_element_id;
SELECT
decode(p_spec_id,0,
decode(p_low_value_lookup,
7,qc.lower_reasonable_limit,
6,qc.lower_spec_limit,
5,qc.lower_user_defined_limit,
4,qc.target_value,
3,qc.upper_user_defined_limit,
2,qc.upper_spec_limit,
1,qc.upper_reasonable_limit,
NULL),
decode(p_low_value_lookup,
7,QscQs.lower_reasonable_limit,
6,QscQs.lower_spec_limit,
5,QscQs.lower_user_defined_limit,
4,QscQs.target_value,
3,QscQs.upper_user_defined_limit,
2,QscQs.upper_spec_limit,
1,QscQs.upper_reasonable_limit,
NULL)) LOW_VALUE,
decode(p_spec_id,0,
decode(p_high_value_lookup,
7,qc.lower_reasonable_limit,
6,qc.lower_spec_limit,
5,qc.lower_user_defined_limit,
4,qc.target_value,
3,qc.upper_user_defined_limit,
2,qc.upper_spec_limit,
1,qc.upper_reasonable_limit,
NULL),
decode(p_high_value_lookup,
7,QscQs.lower_reasonable_limit,
6,QscQs.lower_spec_limit,
5,QscQs.lower_user_defined_limit,
4,QscQs.target_value,
3,QscQs.upper_user_defined_limit,
2,QscQs.upper_spec_limit,
1,QscQs.upper_reasonable_limit,
NULL)) HIGH_VALUE,
nvl(QscQs.uom_code, qc.uom_code) SPEC_CHAR_UOM,
nvl(qpc.uom_code, qc.uom_code) PLAN_CHAR_UOM,
nvl(qpc.decimal_precision, qc.decimal_precision) DECIMAL_PRECISION
FROM
qa_chars qc,
qa_plan_chars qpc,
(select
qsc.CHAR_ID,
qsc.ENABLED_FLAG,
qsc.TARGET_VALUE,
qsc.UPPER_SPEC_LIMIT,
qsc.LOWER_SPEC_LIMIT,
qsc.UPPER_REASONABLE_LIMIT,
qsc.LOWER_REASONABLE_LIMIT,
qsc.UPPER_USER_DEFINED_LIMIT,
qsc.LOWER_USER_DEFINED_LIMIT,
qsc.UOM_CODE
from
qa_spec_chars qsc,
qa_specs qs
where
qsc.spec_id = qs.common_spec_id and
qs.spec_id = p_spec_id) QscQs
WHERE
qpc.plan_id = p_plan_id AND
qpc.enabled_flag = 1 AND
qc.char_id = qpc.char_id AND
qc.char_id = QscQs.char_id (+) AND
qpc.char_id = p_element_id;
SELECT department_id
FROM bom_departments_val_v
WHERE department_code = d_code
AND organization_id = o_id;
SELECT wip_entity_id
FROM wip_discrete_jobs_all_v
WHERE wip_entity_name = w_e_name
AND organization_id = o_id;
SELECT line_id
FROM wip_lines_val_v
WHERE line_code = w_e_name
AND organization_id = o_id;
SELECT resource_id
FROM bom_resources_val_v
WHERE resource_code = r_code
AND organization_id = o_id;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = v_name
AND nvl(end_date_active, sysdate + 1) > sysdate;
SELECT po_header_id
FROM po_pos_val_v
WHERE segment1 = s;
SELECT customer_id
FROM qa_customers_lov_v
WHERE status = 'A'
AND customer_name = c_name
AND nvl(customer_prospect_code, 'CUSTOMER') = 'CUSTOMER';
SELECT sales_order_id header_id
FROM qa_sales_orders_lov_v
WHERE order_number = v;
SELECT sl.line_number
FROM mtl_system_items_kfv msik, so_lines sl
WHERE sl.inventory_item_id = msik.inventory_item_id
AND header_id = h_id;
SELECT pr.po_release_id
FROM po_releases pr
WHERE pr.po_header_id = p_id
AND pr.release_num = r_num;
mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
non-pjm enabled orgs).
rkaza, 11/10/2001.
*/
--
-- the sql has to be changed as pjm_projects_all_v is operating unit sensitive.
-- has to change to a sql that searching all operating units.
-- reference bug 3578563
-- jezheng
-- Mon Apr 19 12:20:16 PDT 2004
--
/* CURSOR c (p_num VARCHAR2) IS
SELECT project_id
FROM pjm_projects_all_v
WHERE project_number = p_num;
select project_id
from pa_projects_all
where segment1 = p_proj_num
UNION ALL
select project_id
from pjm_seiban_numbers
where project_number = p_proj_num;
SELECT task_id
FROM mtl_task_v
WHERE task_number = p_task_number AND project_id = p_project_id;
select TASK_ID
from pa_tasks
where PROJECT_ID = p_proj_id and
task_number = p_task_num;
SELECT sh.header_id
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') and
sh.order_number = v;
SELECT LPN_ID
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LICENSE_PLATE_NUMBER = t_id;
SELECT LPN_ID
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LICENSE_PLATE_NUMBER = t_id;
SELECT k_header_id
FROM oke_k_headers_lov_v
WHERE k_number = val;
SELECT k_line_id
FROM oke_k_lines_full_v
WHERE line_number = val;
SELECT deliverable_id
FROM oke_k_deliverables_vl
WHERE deliverable_num = val;
SELECT WDJ.wip_entity_id
FROM wip_entities WE, wip_discrete_jobs WDJ
WHERE WDJ.status_type in (3,4) and
WDJ.wip_entity_id = WE.wip_entity_id and
WE.entity_type IN (6, 7) and
WE.wip_entity_name = w_e_name
AND WDJ.organization_id = o_id;
SELECT party_id
FROM hz_parties
WHERE status = 'A'
AND party_name = p_name
AND party_type IN ('ORGANIZATION','PERSON')
ORDER BY party_name;
SELECT cii.instance_id
FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
WHERE cii.instance_number = i_num
AND cii.last_vld_organization_id = msik.organization_id;
SELECT cc.counter_id
FROM csi_counters_vl cc
WHERE cc.name = c_name;
SELECT cc.counter_id
FROM cs_counters cc, cs_counter_groups ccg
WHERE cc.counter_group_id = ccg.counter_group_id
AND ccg.template_flag = 'N'
AND cc.name = c_name;
SELECT mr_header_id
FROM qa_ahl_mr
WHERE title = mr_title;
SELECT incident_id
FROM cs_incidents
WHERE incident_number = s_request;
SELECT wip_entity_id
FROM wip_discrete_jobs_all_v
WHERE wip_entity_name = w_e_name
AND organization_id = o_id;
SELECT BATCH_ID
FROM GME_BATCH_HEADER
WHERE BATCH_NO = batch_num
AND ( ORGANIZATION_ID IS NULL OR
ORGANIZATION_ID = o_id );
SELECT BATCHSTEP_ID
FROM GME_BATCH_STEPS
WHERE BATCHSTEP_NO = L_BATCHSTEP_NUM
AND BATCH_ID = L_BATCH_ID;
SELECT OPRN_ID
FROM GMO_BATCH_STEPS_V
WHERE OPERATION = L_OPERATION
AND BATCH_ID = L_BATCH_ID
AND BATCHSTEP_ID = L_BATCHSTEP_ID;
SELECT BATCHSTEP_ACTIVITY_ID
FROM GME_BATCH_STEP_ACTIVITIES
WHERE ACTIVITY = L_ACTIVITY
AND BATCH_ID = L_BATCH_ID
AND BATCHSTEP_ID = L_BATCHSTEP_ID ;
SELECT BATCHSTEP_RESOURCE_ID
FROM GME_BATCH_STEP_RESOURCES
WHERE RESOURCES = L_RESOURCES
AND BATCH_ID = L_BATCH_ID
AND BATCHSTEP_ID = L_BATCHSTEP_ID
AND BATCHSTEP_ACTIVITY_ID = L_ACTIVITY_ID;
SELECT GP.PARAMETER_ID
FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE
WHERE GP.PARAMETER_NAME = L_PARAMETER
AND GP.PARAMETER_ID = GE.PARAMETER_ID
AND GE.BATCHSTEP_RESOURCE_ID= L_RESOURCE_ID;
select repair_line_id
from csd_repairs
where repair_number = p_ro_num;
select task_id
from jtf_tasks_b
where task_number = p_task_num;
SELECT 1
FROM mtl_secondary_inventories
WHERE organization_id = x_org_id
AND nvl(disable_date, sysdate+1) > sysdate
AND secondary_inventory_name = x_to_subinventory;
new_sql_statement := 'SELECT 1 FROM DUAL WHERE ' || '''' ||
qa_core_pkg.dequote(value) || '''';
SELECT 1
FROM mtl_item_uoms_view
WHERE inventory_item_id = x_item_id AND
organization_id = x_org_id AND
uom_code = x_uom_code;
SELECT 1
FROM mtl_item_revisions
WHERE inventory_item_id = x_item_id AND
organization_id = x_org_id AND
revision = x_revision;
SELECT 1
FROM mtl_lot_numbers
WHERE inventory_item_id = x_item_id AND
organization_id = x_org_id AND
lot_number = x_lot_num;
SELECT 1
FROM mtl_serial_numbers
WHERE inventory_item_id = x_item_id AND
current_organization_id = x_org_id AND
(x_lot_num IS NULL OR lot_number = x_lot_num) AND
(x_revision IS NULL OR revision = x_revision) AND
serial_number = x_serial_num;
SELECT 1
FROM mtl_secondary_inventories
WHERE organization_id = x_org_id
AND ((((SUBINVENTORY_TYPE <> 2) OR (SUBINVENTORY_TYPE IS NULL))
AND nvl(disable_date, sysdate+1) > sysdate)
OR (SUBINVENTORY_TYPE = 2))
AND secondary_inventory_name = x_subinventory;
SELECT 1
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = x_transaction_id
AND lot_number = x_lot_number;
SELECT 1
FROM mtl_serial_numbers msn,
mtl_transaction_lots_temp mtlt
WHERE msn.lot_line_mark_id = x_transaction_id
AND mtlt.transaction_temp_id = msn.lot_line_mark_id
AND mtlt.serial_transaction_temp_id = msn.line_mark_id
AND mtlt.lot_number = x_lot_number
AND x_lot_number IS NOT NULL
AND msn.serial_number = x_serial_number
UNION ALL
SELECT 1
FROM mtl_serial_numbers msn
WHERE msn.line_mark_id = x_transaction_id
AND x_lot_number IS NULL
AND msn.serial_number = x_serial_number;
SELECT 1
FROM wip_operations_all_v
WHERE organization_id = x_org_id
AND wip_entity_id = x_wip_entity_id
AND operation_seq_num = x_op_seq_number;
SELECT 1
FROM wip_operations_all_v
WHERE organization_id = x_org_id
AND wip_entity_id = x_wip_entity_id
AND operation_seq_num = x_op_seq_number
AND repetitive_schedule_id =
( SELECT repetitive_schedule_id
FROM wip_first_open_schedule_v
WHERE organization_id = x_org_id
AND wip_entity_id = x_wip_entity_id
AND line_id = x_line_id );
SELECT 1
FROM wip_operations_all_v
WHERE organization_id = x_org_id
AND wip_entity_id = x_maintenance_work_order_id
AND operation_seq_num = x_maintenance_op_seq;
SELECT 1
FROM po_lines_val_v
WHERE po_header_id = x_po_header_id
AND line_num = x_po_line_number;
SELECT 1
FROM po_line_locations
WHERE po_line_id =
( SELECT po_line_id
FROM po_lines
WHERE line_num = x_po_line_num
AND po_header_id = x_po_header_id)
AND shipment_num = x_po_shipments;
SELECT 1
FROM po_shipments_all_v
WHERE po_line_id =
(SELECT po_line_id
FROM po_lines_val_v
WHERE line_num = x_po_line_num
AND po_header_id = x_po_header_id)
AND shipment_num = x_po_shipments;
SELECT 1
FROM RCV_SHIPMENT_HEADERS
WHERE receipt_num = x_receipt_number
AND RECEIPT_SOURCE_CODE = 'VENDOR';
SELECT 1
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 AND
receipt_num = x_receipt_number;
SELECT 1
FROM rcv_receipts_all_v
WHERE receipt_num = x_receipt_number;
SELECT assigned_char_id
FROM qa_plan_char_actions
WHERE plan_char_action_id = pca_id;
sql_string := 'SELECT short_code, description
FROM qa_plan_char_value_lookups
WHERE plan_id = :1
AND char_id = :2
AND short_code LIKE :3
ORDER BY short_code';
'select *
from
(select ''x'' code, ''x'' description
from dual
where 1 = 2
union
select * from
( '|| sql_string ||
' )) where code like :1';
sql_string := 'SELECT department_code, description
FROM bom_departments_val_v
WHERE department_code like :1 AND
organization_id = :2
ORDER BY department_code';
sql_string := 'SELECT wip_entity_name, description
FROM wip_discrete_jobs_all_v
WHERE wip_entity_name like :1 AND
organization_id = :2
ORDER BY wip_entity_name';
sql_string := '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) and
WE.wip_entity_name like :2
order by WE.wip_entity_name';
sql_string := 'SELECT line_code, description
FROM wip_lines_val_v
WHERE line_code like :1 AND
organization_id = :2
ORDER BY line_code';
sql_string := 'SELECT resource_code, description
FROM bom_resources_val_v
WHERE resource_code like :1
AND organization_id = :2
ORDER BY resource_code';
sql_string := 'SELECT vendor_name, segment1
FROM po_vendors
WHERE vendor_name like :1
AND nvl(end_date_active, sysdate + 1) > sysdate
ORDER BY vendor_name';
sql_string := 'SELECT po_header_id, segment1, vendor_name ||
'' ('' || operating_unit || '')''
FROM qa_po_numbers_lov_v
WHERE segment1 like :1
ORDER BY segment1';
sql_string := 'SELECT customer_name, customer_number
FROM qa_customers_lov_v
WHERE customer_name like :1
AND status = ''A''
AND nvl(customer_prospect_code, ''CUSTOMER'') =
''CUSTOMER''
ORDER BY customer_name';
sql_string := 'SELECT order_number, order_type name
FROM qa_sales_orders_lov_v
WHERE order_number like :1
ORDER BY order_number';
sql_string := '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
AND header_id like :1';
sql_string := 'SELECT release_num, release_date
FROM po_releases pr
WHERE pr.release_num like :1
ORDER BY pr.release_num';
sql_string := 'SELECT release_num, release_date
FROM po_releases
WHERE po_header_id = :1
AND release_num like :2
ORDER BY 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.
*/
/* Before Single Scan LOV
IF value IS NULL THEN
wild := '%';
sql_string := 'SELECT project_number, project_name
FROM pjm_projects_v
WHERE project_number like :1
ORDER BY project_number';
sql_string := 'SELECT task_number, task_name
FROM mtl_task_v
WHERE task_number like :1
ORDER BY task_number';
sql_string := 'SELECT 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'') and
sh.order_number like :1
ORDER BY sh.order_number';
sql_string := 'select distinct to_char(oel.line_number),
''RMA Number: '' || sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
sql_string := 'SELECT license_plate_number, attribute1
FROM wms_license_plate_numbers
WHERE license_plate_number like :1
ORDER BY license_plate_number';
sql_string := 'SELECT license_plate_number, attribute1
FROM wms_license_plate_numbers
WHERE license_plate_number like :1
ORDER BY license_plate_number';
sql_string := 'SELECT k_number, short_description
FROM oke_k_headers_lov_v
WHERE k_number like :1
ORDER BY k_number';
sql_string := 'SELECT line_number, line_description
FROM oke_k_lines_full_v
WHERE header_id = :1 AND
line_number like :2
ORDER BY line_number';
sql_string := 'SELECT deliverable_num, description
FROM oke_k_deliverables_vl
WHERE k_header_id = :1 AND
k_line_id = :2 AND
deliverable_num like :3
ORDER BY deliverable_num';
sql_string := 'SELECT uom_code, description
FROM mtl_item_uoms_view
WHERE inventory_item_id = :1
AND organization_id = :2
AND uom_code like :3
ORDER BY uom_code';
sql_string := 'SELECT mir.revision, mir.effectivity_date
FROM mtl_item_revisions mir, mtl_system_items msi
WHERE mir.inventory_item_id = :1
AND mir.organization_id = :2
AND mir.revision like :3
AND mir.inventory_item_id = msi.inventory_item_id
AND mir.organization_id = msi.organization_id
AND msi.revision_qty_control_code = 2
ORDER BY revision';
sql_string := 'select lot_number, description
from mtl_lot_numbers
where inventory_item_id = :1
and organization_id = :2
and lot_number like :3
and (disable_flag = 2 or disable_flag is null)
ORDER BY lot_number';
sql_string := 'select serial_number, current_status_name
from mtl_serial_numbers_all_v
where current_organization_id = :1
and inventory_item_id = :2
and (:3 is null OR lot_number = :4)
and (:5 is null OR revision = :6)
and serial_number like :7
order by 1';
sql_string := '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 and
cii.inventory_item_id = nvl(:2, cii.inventory_item_id) and
cii.instance_number like :3 and
cii.serial_number = nvl(:4, cii. serial_number)
order by cii.instance_number';
sql_string := '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
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
msn.serial_number like :3 and
cii.instance_id= nvl(:4, cii.instance_id)
order by msn.serial_number';
sql_string := 'SELECT secondary_inventory_name, description
FROM mtl_secondary_inventories
WHERE organization_id = :1
AND nvl(disable_date, sysdate+1) > sysdate
AND secondary_inventory_name like :2
ORDER BY secondary_inventory_name';
sql_string := 'SELECT lot_number, lot_expiration_date
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = :1
AND lot_number like :2
ORDER BY lot_number';
sql_string := 'SELECT msn.serial_number, msn.current_status
FROM mtl_serial_numbers msn,
mtl_transaction_lots_temp mtlt
WHERE msn.lot_line_mark_id = :1
AND mtlt.transaction_temp_id = msn.lot_line_mark_id
AND mtlt.serial_transaction_temp_id = msn.line_mark_id
AND mtlt.lot_number = :2
AND :3 IS NOT NULL
AND msn.serial_number like :4
UNION ALL
SELECT msn.serial_number, msn.current_status
FROM mtl_serial_numbers msn
WHERE msn.line_mark_id = :5
AND :6 IS NULL
AND msn.serial_number like :7
ORDER BY 1';
sql_string := 'SELECT operation_seq_num, operation_code
FROM wip_operations_all_v
WHERE operation_seq_num like :1
AND wip_entity_id = :2
AND organization_id = :3
ORDER BY operation_seq_num';
sql_string := 'SELECT operation_seq_num, operation_code
FROM wip_operations_all_v
WHERE operation_seq_num like :1
AND wip_entity_id = :2
AND organization_id = :3
AND repetitive_schedule_id =
(
SELECT repetitive_schedule_id
FROM wip_first_open_schedule_v
WHERE line_id = :4
AND wip_entity_id = :5
AND organization_id = :6
)
ORDER BY operation_seq_num';
sql_string := 'SELECT operation_seq_num, operation_code
FROM wip_operations_all_v
WHERE operation_seq_num like :1
AND wip_entity_id = :2
AND organization_id = :3
ORDER BY operation_seq_num';
sql_string := 'SELECT line_num, concatenated_segments
FROM po_lines_val_v
WHERE po_header_id = :1
AND line_num like :2
ORDER BY line_num';
sql_string := 'SELECT 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)
AND shipment_num like :3';
sql_string := 'SELECT shipment_num, shipment_type
FROM po_line_locations
WHERE po_line_id =
(SELECT po_line_id
FROM po_lines
WHERE line_num = :1
AND po_header_id = :2)
AND shipment_num like :3';
/*sql_string := 'SELECT RCVSH.receipt_num, POV.vendor_name
FROM rcv_receipts_all_v
WHERE receipt_num like :1
ORDER BY receipt_num';
sql_string := 'SELECT 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 AND
rcvsh.receipt_num like :1
ORDER BY RCVSH.receipt_num';
sql_string := 'SELECT party_number, party_name
FROM hz_parties
WHERE party_number like :1
AND status = ''A''
AND party_type IN (''ORGANIZATION'',''PERSON'')
ORDER BY party_name';
sql_string := 'SELECT party_name, party_number
FROM hz_parties
WHERE party_name like :1
AND status = ''A''
AND party_type IN (''ORGANIZATION'',''PERSON'')
ORDER BY party_name';
sql_string := '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
AND instance_number like :1
ORDER BY 1';
sql_string := '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''
AND cc.name like :1
ORDER BY 1';
sql_string := 'SELECT name, description
FROM csi_counters_vl
WHERE name like :1
AND trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate))
AND nvl(end_date_active, trunc(sysdate))
ORDER BY 1';
sql_string := 'SELECT title, version_number
FROM qa_ahl_mr
WHERE title like :1
ORDER BY 1';
sql_string := 'SELECT incident_number, summary
FROM cs_incidents
WHERE incident_number like :1
ORDER BY 1';
sql_string := 'SELECT wip_entity_name, description
FROM wip_discrete_jobs_all_v
WHERE wip_entity_name like :1 AND
organization_id = :2
ORDER BY wip_entity_name';
sql_string := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
AND concatenated_segments like :2
ORDER BY concatenated_segments';
sql_string := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
AND concatenated_segments like :2
ORDER BY concatenated_segments';
sql_string := 'SELECT concatenated_segments, description
FROM mtl_item_locations_kfv
WHERE organization_id = :1
AND concatenated_segments like :2
ORDER BY concatenated_segments';
sql_string := 'SELECT secondary_inventory_name, description
FROM mtl_secondary_inventories
WHERE organization_id = :1
AND nvl(disable_date, sysdate+1) > sysdate
AND secondary_inventory_name like :2
ORDER BY secondary_inventory_name';
sql_string := 'SELECT mms.status_code, mms.description
FROM mtl_lot_numbers mln, mtl_material_statuses mms
WHERE mln.inventory_item_id = :1
AND mln.organization_id = :2
AND mln.lot_number like :3
AND mln.status_id = mms.status_id
AND mms.status_code like :4
AND mms.enabled_flag = 1';
SELECT status_id
FROM mtl_material_statuses
WHERE status_code = code;
sql_string := 'SELECT mms.status_code, mms.description
FROM mtl_serial_numbers msn, mtl_material_statuses mms
WHERE msn.inventory_item_id = :1
AND msn.serial_number like :2
AND msn.status_id = mms.status_id
AND mms.status_code like :3
AND mms.enabled_flag = 1';
sql_string := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC '||
'FROM GME_BATCH_HEADER '||
'WHERE BATCH_NO like :1 '||
'AND (ORGANIZATION_ID = :2 '||
' or ORGANIZATION_ID IS NULL)';
sql_string := 'SELECT STEPS.BATCHSTEP_NO,OPS.OPRN_DESC BATCHSTEP_DESC '||
'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
'WHERE STEPS.BATCHSTEP_NO like :1 '||
'AND STEPS.BATCH_ID =:2 '||
'AND STEPS.OPRN_ID = OPS.OPRN_ID';
sql_string := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
'FROM GMO_BATCH_STEPS_V '||
'WHERE OPERATION like :1 '||
'AND BATCHSTEP_ID = :2 '||
'AND BATCH_ID =:3';
sql_string := 'SELECT STEPS.ACTIVITY,ACTIVITIES.ACTIVITY_DESC '||
'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
'WHERE STEPS.ACTIVITY like :1 '||
'AND STEPS.BATCHSTEP_ID =:2 '||
'AND STEPS.BATCH_ID =:3 '||
'AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY';
sql_string := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
'WHERE GBSR.RESOURCES like :1 '||
'AND GBSR.BATCHSTEP_ACTIVITY_ID =:2 '||
'AND GBSR.BATCHSTEP_ID =:3 '||
'AND GBSR.BATCH_ID =:4 '||
'AND GBSR.RESOURCES = CRMV.RESOURCES';
sql_string := 'SELECT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
'FROM GMP_PROCESS_PARAMETERS GP,GME_PROCESS_PARAMETERS GE '||
'WHERE GE.RESOURCES = :1 '||
'AND GE.PARAMETER_ID = GP.PARAMETER_ID '||
'AND GP.PARAMETER_NAME like :2';
sql_string := 'SELECT repair_number, problem_description
FROM csd_repairs
WHERE repair_number like :1
and status not in (''C'', ''H'')
ORDER BY repair_number';
sql_string := 'SELECT task_number, task_name
FROM jtf_tasks_vl
WHERE task_number like :1
ORDER BY task_number';
SELECT status_id
FROM mtl_material_statuses
WHERE status_code = code;
sql_string := 'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1
AND concatenated_segments like :2
ORDER BY concatenated_segments';
'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1 AND
concatenated_segments like :2 AND
inventory_item_id IN (
SELECT inventory_item_id
FROM wip_requirement_operations
WHERE wip_entity_id = :3 AND
organization_id = :4)';
'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1 AND
concatenated_segments like :2 AND
inventory_item_id IN (
SELECT inventory_item_id
FROM wip_requirement_operations
WHERE wip_entity_id = (
SELECT wip_entity_id
FROM wip_repetitive_items
WHERE line_id = :3 AND
primary_item_id = :4 AND
organization_id = :5))';
'SELECT concatenated_segments, description
FROM mtl_system_items_kfv
WHERE organization_id = :1 AND
concatenated_segments like :2 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 = :3 AND
bom.organization_id = :4)';
l_sql_string := 'SELECT ''x'', ''x'' FROM dual WHERE 1 = 2';
sql_string := '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
and msikfv.concatenated_segments like :2
order by msikfv.concatenated_segments';
sql_string := '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
AND meaav.activity like :3
ORDER BY meaav.activity';
sql_string := '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
AND msib.concatenated_segments like :3
ORDER BY msib.concatenated_segments';
sql_string := '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
AND meaav.activity like :3
ORDER BY meaav.activity';
sql_string := '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
AND msi.concatenated_segments like :3
ORDER BY msi.concatenated_segments';
sql_string := 'SELECT concatenated_segments, description
FROM mtl_item_locations_kfv
WHERE organization_id = :1
AND concatenated_segments like :2
ORDER BY concatenated_segments';
SELECT 'x', 'x' FROM dual WHERE 1 = 2;
SELECT
target_value,
lower_spec_limit,
upper_spec_limit,
lower_user_defined_limit,
upper_user_defined_limit,
lower_reasonable_limit,
upper_reasonable_limit
FROM qa_spec_chars
WHERE spec_id = x_spec_id
AND char_id = x_char_id;
SELECT name
FROM qa_chars
WHERE char_id =
( SELECT char_id
FROM qa_specs
WHERE spec_id = x_spec_id );
SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'QA_SPEC_TYPE'
AND lookup_code =
( SELECT assignment_type
FROM qa_specs
WHERE spec_id = p_spec_id );
SELECT organization_id, item_id
FROM qa_specs
WHERE spec_id = p_spec_id;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id =
( SELECT vendor_id
FROM qa_specs
WHERE spec_id = p_spec_id );
SELECT customer_name
FROM qa_customers_lov_v
WHERE customer_id =
( SELECT customer_id
FROM qa_specs
WHERE spec_id = p_spec_id );
SELECT 1
FROM qa_txn_collection_triggers qtct
WHERE qtct.transaction_number = txn_number
AND qtct.collection_trigger_id = element_id;
SELECT cii.instance_id
FROM csi_item_instances cii
WHERE cii.instance_number = i_num;
SELECT cii.instance_id
FROM csi_item_instances cii
WHERE cii.inventory_item_id = p_asset_group_id
AND cii.serial_number = p_asset_number; --inv id and serial num combo is unique
SELECT msikfv.inventory_item_id
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 = o_id
and msikfv.concatenated_segments = a_group
and rownum=1;
SELECT 1
FROM MTL_TRANSACTION_TYPES
WHERE transaction_source_type_id = 5
AND transaction_action_id in (31,32)
AND transaction_type_name = p_value;
SELECT 1
FROM MTL_TRANSACTION_TYPES
WHERE transaction_source_type_id = 5
AND transaction_action_id in (30,31,32)
AND transaction_type_name = p_value;
SELECT 1
FROM MFG_LOOKUPS
WHERE lookup_type ='WIP_MOVE_TRANSACTION_TYPE'
AND meaning = p_value;
SELECT 1
FROM QA_LOOKUPS
WHERE lookup_type = p_lookup_type
AND lookup_code = p_value;
str := 'Select '|| qa_chars_api.hardcoded_column(p_child_char_id)||
' from qa_results '||
' where plan_id = :p_plan_id '||
' and collection_id = :p_collection_id '||
' and occurrence = :p_occurrence';
SELECT (pll.quantity_received - (pll.quantity_accepted + pll.quantity_rejected)) quantity_received
FROM po_headers_all ph,
po_line_locations_all pll,
po_lines_all pl
WHERE pll.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pll.shipment_num = p_ship_num
AND pl.line_num = p_line_num
AND ph.segment1 = p_po_num;
SELECT stock_locator_control_code,
negative_inv_receipt_code
FROM mtl_parameters
WHERE organization_id = org_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE organization_id = org_id
AND nvl(disable_date, sysdate + 1) > sysdate
AND secondary_inventory_name = p_subinv_name;
SELECT
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.location_control_code,
msi.revision_qty_control_code,
msi.serial_number_control_code,
msi.lot_control_code,
msi.primary_uom_code
FROM
mtl_system_items msi
WHERE msi.organization_id = org_id AND
msi.inventory_item_id = p_item_id;
select char_id from qa_plan_chars
where plan_id = p_plan_id;
SELECT qpc.char_id bulk collect
INTO hardcoded_char_tab
FROM qa_plan_chars qpc
WHERE qpc.plan_id = p_plan_id
AND qpc.information_flag = 1
ORDER BY prompt_sequence;
Select upper(translate(name,' ''*{}','_____')) into char_name
from qa_chars
where char_id = hardcoded_char_tab(cntr);
SELECT deref_view_name INTO plan_name
FROM qa_plans
WHERE plan_id = p_plan_id;
EXECUTE IMMEDIATE 'Select ' || cols_str || ' from ' || plan_name ||
' where collection_id = :collection_id and
occurrence = :occurrence'
INTO p_values_string
USING p_collection_id,
p_occurrence;