[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x' FROM ahl_visits_b
WHERE VISIT_ID = p_visit_id;
SELECT department_id FROM bom_departments dept, hr_all_organization_units org
WHERE org.name = p_org_name AND
dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
dept.description = p_dept_name;
SELECT 'x' FROM bom_departments WHERE
department_id = l_dept_id;
SELECT 'x' FROM ahl_simulation_plans_b WHERE
simulation_plan_id = l_plan_id;
SELECT department_id, org.organization_id, dept.description dept_name
FROM bom_departments dept, hr_all_organization_units org
WHERE org.name = p_org_name AND
dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID) AND
( (p_dept_name is not null and dept.description = p_dept_name)
or p_dept_name is null ) ;
SELECT description FROM bom_departments WHERE
department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
SELECT name FROM hr_all_organization_units WHERE
name = l_org_name;
SELECT 'x' FROM ahl_simulation_plans_b WHERE
simulation_plan_id = l_plan_id;
SELECT d.resource_code,
d.description,
d.resource_type,
m.meaning resource_type_mean
FROM bom_resources d, mfg_lookups m
WHERE d.resource_id = l_resource_id
AND d.resource_type = m.lookup_code
AND m.lookup_type = 'BOM_RESOURCE_TYPE';
SELECT department_id,org.organization_id FROM bom_departments dept, hr_all_organization_units org
WHERE org.name = p_org_name AND
dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
dept.description = p_dept_name and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID);
SELECT 'x' FROM bom_departments WHERE
department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
SELECT 'x' FROM ahl_simulation_plans_b WHERE
simulation_plan_id = l_plan_id;
SELECT VISIT_ID
FROM ahl_visits_b v
WHERE (DEPARTMENT_ID = p_dept_id OR
(DEPARTMENT_ID is not null and exists (select 1
FROM ahl_visit_tasks_b
where visit_id =v.visit_id
AND department_id =p_dept_id
AND nvl(status_code,'x') ='PLANNING'))) AND
SIMULATION_PLAN_ID = p_plan_id AND
STATUS_CODE IN ('PLANNING','PARTIALLY RELEASED') AND
START_DATE_TIME IS NOT NULL AND
trunc(START_DATE_TIME) <= p_end_time AND
AHL_VWP_TIMES_PVT.get_visit_end_time(visit_id) >=p_start_time AND
SIMULATION_DELETE_FLAG = 'N';
SELECT VISIT_ID
FROM ahl_visits_b a
WHERE (DEPARTMENT_ID = p_dept_id OR
(DEPARTMENT_ID is not null and exists (select 1
FROM ahl_visit_tasks_b
where visit_id =a.visit_id
AND department_id =p_dept_id
AND nvl(status_code,'x') ='PLANNING'))) AND
SIMULATION_PLAN_ID IN (SELECT SIMULATION_PLAN_ID FROM ahl_simulation_plans_b WHERE
PRIMARY_PLAN_FLAG = G_PLAN_TYPE_PRIMARY) AND
NOT EXISTS (SELECT 1 FROM ahl_visits_b WHERE SIMULATION_PLAN_ID = p_plan_id
AND ASSO_PRIMARY_VISIT_ID = a.VISIT_ID) AND
STATUS_CODE IN ('PLANNING','PARTIALLY RELEASED') AND
START_DATE_TIME IS NOT NULL AND
trunc(START_DATE_TIME) <= p_end_time AND
AHL_VWP_TIMES_PVT.get_visit_end_time(visit_id) >=p_start_time;
SELECT PRIMARY_PLAN_FLAG FROM ahl_simulation_plans_b
WHERE SIMULATION_PLAN_ID = p_plan_id;
SELECT vt.visit_task_id, mr_route_id,
NVL(start_from_hour, 0) start_from_hour,
NVL(duration, 0) duration,
start_date_time,
end_date_time
FROM ahl_visit_tasks_b vt
WHERE vt.visit_id = p_visit_id AND
status_code = 'PLANNING' AND
(department_id = p_dept_id OR (department_id is NULL AND
p_dept_id = (select department_id from ahl_visits_b
where visit_id = p_visit_id))) AND
mr_route_id is not null
ORDER BY vt.visit_task_id;
SELECT start_date_time,department_id,close_date_time, organization_id
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT a.MR_ROUTE_ID,B.route_id
FROM ahl_visit_tasks_b a, ahl_mr_routes_app_v B
WHERE VISIT_TASK_ID = p_task_id
AND a.mr_route_id = B.mr_route_id;
SELECT vt.DEPARTMENT_ID,v.department_id,v.organization_id
FROM ahl_visits_b v, ahl_visit_tasks_b vt
WHERE vt.VISIT_TASK_ID = p_task_id
AND v.visit_id = vt.visit_id;
SELECT ASO_RESOURCE_ID
FROM ahl_rt_oper_resources WHERE
ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE
/*LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
AND NVL(SCHEDULED_TYPE_ID,1) <> 2
AND OBJECT_ID = c_route_id;
SELECT ASO_RESOURCE_ID
FROM ahl_rt_oper_resources WHERE
ASSOCIATION_TYPE_CODE = G_OPER_ASSOC_TYPE
/*LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
AND NVL(SCHEDULED_TYPE_ID,1) <> 2
AND OBJECT_ID IN (SELECT OPERATION_ID FROM ahl_route_operations WHERE ROUTE_ID = c_route_id);
SELECT BOM_RESOURCE_ID
FROM ahl_resource_mappings WHERE
ASO_RESOURCE_ID = c_aso_resource_id
AND bom_org_id = c_org_id;
SELECT a.RESOURCE_TYPE,a.RESOURCE_CODE,a.DESCRIPTION,
B.CAPACITY_UNITS, M.meaning resource_type_mean
FROM bom_resources A,
bom_department_resources B,
mfg_lookups M
WHERE a.resource_id = B.resource_id
AND B.resource_id = p_bom_resource_id
AND B.department_id = p_bom_department_id
AND A.resource_type = M.lookup_code
AND M.lookup_type = 'BOM_RESOURCE_TYPE';
SELECT SHIFT_NUM,
CAPACITY_UNITS SHIFT_CAPACITY
FROM bom_resource_shifts
WHERE resource_id = p_bom_resource_id
AND department_id = p_bom_department_id;
SELECT department_code,description
FROM bom_departments
WHERE department_id = p_bom_department_id;
SELECT NAME, DESCRIPTION FROM ahl_resources WHERE
resource_id = p_rsrc_id;
SELECT b.RESOURCE_TYPE, b.RESOURCE_CODE, b.DESCRIPTION, m.meaning
FROM bom_resources b, mfg_lookups m
WHERE b.resource_type = m.lookup_code
AND m.lookup_type = 'BOM_RESOURCE_TYPE'
AND resource_id = p_rsrc_id;
SELECT COUNT(ro.operation_id)
FROM ahl_route_operations ro, AHL_OPERATIONS_VL OP
WHERE OP.operation_id=RO.operation_id
AND ro.ROUTE_ID = p_route_id
AND OP.revision_number IN
( SELECT MAX(OP1.revision_number)
FROM AHL_OPERATIONS_B_KFV OP1
WHERE OP1.concatenated_segments=OP.concatenated_segments
AND OP1.revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
);
SELECT COUNT(*) FROM ahl_rt_oper_resources
WHERE OBJECT_ID = p_route_id AND
ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
SELECT RO.OPERATION_ID, RO.STEP
FROM ahl_route_operations ro, AHL_OPERATIONS_VL OP
WHERE OP.operation_id=RO.operation_id
AND ro.ROUTE_ID = p_route_id
AND OP.revision_number IN
( SELECT MAX(OP1.revision_number)
FROM AHL_OPERATIONS_B_KFV OP1
WHERE OP1.concatenated_segments=OP.concatenated_segments
AND OP1.revision_status_code='COMPLETE'
AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
)
ORDER BY RO.STEP;
SELECT ROUTE_ID FROM ahl_mr_routes_app_v
WHERE MR_ROUTE_ID = p_mr_route_id;
SELECT a.aso_resource_id,
a.quantity,
b.bom_resource_id
FROM ahl_rt_oper_resources a,
ahl_resource_mappings b,
bom_department_resources c
WHERE a.aso_resource_id = b. aso_resource_id
AND a.object_id = l_route_id
AND b.bom_resource_id = c.resource_id
AND b.bom_org_id = l_org_id
AND c.department_id = l_dept_id
/*B6459500 - LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
AND nvl(scheduled_type_id,1) <> 2
AND ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
SELECT a.aso_resource_id,
a.quantity,
b.bom_resource_id
FROM ahl_rt_oper_resources a,
ahl_resource_mappings b,
bom_department_resources c
WHERE a.object_id = l_operation_id
AND a.ASSOCIATION_TYPE_CODE = 'OPERATION'
AND a.aso_resource_id = b. aso_resource_id
AND b.bom_resource_id = c.resource_id
AND c.department_id = l_dept_id
/*B6459500 - LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
AND nvl(scheduled_type_id,1) <> 2
AND b.bom_org_id = l_org_id;
SELECT QUANTITY FROM ahl_rt_oper_resources WHERE
ASO_RESOURCE_ID = p_aso_rsrc_id AND
OBJECT_ID = p_object_id AND
ASSOCIATION_TYPE_CODE = p_object_type;
SELECT calendar_code, shift_num FROM ahl_department_shifts
WHERE department_id = p_department_id;
SELECT FROM_TIME, TO_TIME FROM bom_shift_times
WHERE CALENDAR_CODE = p_calendar_code AND
SHIFT_NUM = p_shift_num;
SELECT DAYS_ON, DAYS_OFF FROM bom_workday_patterns
WHERE CALENDAR_CODE = p_calendar_code AND
SHIFT_NUM = p_shift_num;
SELECT CALENDAR_START_DATE, CALENDAR_END_DATE FROM bom_calendars
WHERE CALENDAR_CODE = p_calendar_code;
SELECT description FROM bom_departments
WHERE organization_id = c_org_id
AND department_id = c_dept_id;
SELECT LAST_DAY(l_temp_start) INTO l_temp_start FROM DUAL;
SELECT VISIT_NAME, VISIT_NUMBER FROM ahl_visits_vl WHERE
VISIT_ID = p_visit_id;
SELECT VISIT_TASK_NAME,TASK_TYPE_CODE FROM ahl_visit_tasks_vl WHERE
VISIT_TASK_ID = p_task_id;
SELECT B.department_id,A.organization_id FROM
HR_ALL_ORGANIZATION_UNITS A, BOM_DEPARTMENTS B
WHERE A.organization_id = B.organization_id
AND a.name = c_org_name
AND b.description = c_dept_name;
SELECT route_id FROM ahl_mr_routes_app_v
WHERE mr_route_id = p_route_id;
SELECT NVL(time_span, 0) FROM ahl_routes_b
WHERE route_id = p_route_id;
SELECT OPERATION_ID FROM ahl_route_operations
WHERE route_id = p_route_id;
SELECT NVL(MAX(duration), 0) FROM ahl_rt_oper_resources
WHERE OBJECT_ID = p_operation_id AND
ASSOCIATION_TYPE_CODE = G_OPER_ASSOC_TYPE;
SELECT NVL(MAX(duration), 0) FROM ahl_rt_oper_resources
WHERE OBJECT_ID = p_route_id AND
ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
SELECT calendar_code, shift_num FROM ahl_department_shifts
WHERE department_id = p_department_id;
SELECT FROM_TIME, TO_TIME FROM bom_shift_times
WHERE CALENDAR_CODE = p_calendar_code AND
SHIFT_NUM = p_shift_num;
SELECT DAYS_ON, DAYS_OFF FROM bom_workday_patterns
WHERE CALENDAR_CODE = p_calendar_code AND
SHIFT_NUM = p_shift_num;
SELECT CALENDAR_START_DATE, CALENDAR_END_DATE FROM bom_calendars
WHERE CALENDAR_CODE = p_calendar_code;
SELECT EXCEPTION_DATE FROM bom_calendar_exceptions
WHERE CALENDAR_CODE = p_calendar_code AND
EXCEPTION_TYPE = G_HOLIDAY_TYPE
ORDER BY EXCEPTION_DATE;
SELECT description FROM bom_departments
WHERE department_id = c_dept_id;
G_WORKING_DATES_TBL.DELETE;
SELECT 'x' INTO l_junk FROM AHL_DEPARTMENT_SHIFTS ADS, BOM_CALENDAR_EXCEPTIONS BCE
WHERE ADS.DEPARTMENT_ID = p_dept_id AND
BCE.CALENDAR_CODE = ADS.CALENDAR_CODE AND
BCE.EXCEPTION_TYPE = G_HOLIDAY_TYPE AND
TRUNC(BCE.EXCEPTION_DATE) = TRUNC(p_curr_date);
SELECT from_time, to_time
FROM bom_shift_times st, bom_resource_shifts rs
WHERE st.shift_num = rs.shift_num AND
rs.resource_id = p_resource_id;
SELECT B.CAPACITY_UNITS,a.description
FROM bom_resources A,
bom_department_resources B
WHERE a. resource_id = B.resource_id
AND B.resource_id = p_bom_resource_id
AND B.department_id = p_bom_department_id;
SELECT CAPACITY_UNITS SHIFT_CAPACITY
FROM bom_resource_shifts
WHERE resource_id = p_bom_resource_id
AND department_id = p_bom_department_id
AND SHIFT_NUM = ( select shift_num
FROM AHL_DEPARTMENT_SHIFTS
WHERE department_id = p_bom_department_id);
SELECT wip.wip_entity_id
FROM wip_discrete_jobs wip
WHERE wip.organization_id = c_org_id
AND (p_start_date BETWEEN wip.scheduled_start_date
AND wip.scheduled_completion_date
OR
p_end_date BETWEEN wip.scheduled_start_date
AND wip.scheduled_completion_date)
AND not exists (select wip_entity_id
from ahl_workorders wo
where wo.wip_entity_id = wip.wip_entity_id
and wo.status_code = '17');
SELECT wip_entity_id
FROM wip_operations
WHERE wip_entity_id = c_wip_entity_id
AND department_id = c_dept_id;
SELECT SUM(assigned_units)
FROM wip_operation_resources
WHERE wip_entity_id = c_wip_entity_id
AND resource_id = c_resource_id;
SELECT person_id,
instance_id,
effective_start_date,effective_end_date
FROM bom_resource_employees
WHERE resource_id = c_resource_id;
SELECT COUNT(*)
FROM wip_op_resource_instances
WHERE instance_id = c_instance_id
AND (c_start_date BETWEEN start_date AND
completion_date) OR
(c_end_date BETWEEN start_date AND
completion_date);
SELECT wor.start_date, wor.completion_date, wor.assigned_units, wor.wip_entity_id
FROM wip_operation_resources wor, wip_operations wo, wip_discrete_jobs wdj,
AHL_WORKORDERS aw
WHERE wor.resource_id = p_resource_id
AND wo.department_id = p_dept_id
AND wo.wip_entity_id = wor.wip_entity_id
AND wdj.WIP_ENTITY_ID = aw.WIP_ENTITY_ID
AND aw.STATUS_CODE not in ('17','22','7','12','18','4','5')
--(17-Draft, 22-Deleted, 7-Cancelled, 12-Closed, 18-Deferrred, 4-Complete, 5-Complete No-charge)
AND wdj.organization_id = p_org_id
AND wdj.wip_entity_id = wo.wip_entity_id
AND ((wor.start_date BETWEEN p_start_date and p_end_date) OR
(wor.completion_date BETWEEN p_start_date and p_end_date) OR
(wor.start_date < p_start_date AND wor.completion_date > p_end_date));