[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM AHL_FLEET_UTILIZATION;
SELECT fleet_header_id,
name,
operating_org_id
FROM ahl_fleet_headers_b
WHERE fleet_header_id IN
(SELECT Fleet_header_id FROM ahl_fleet_unit_assocs
where simulation_plan_id = p_sim_plan_id
)
AND status_code = 'COMPLETE';
SELECT simulation_plan_id
FROM ahl_simulation_plans_b
WHERE primary_plan_flag = 'Y'
and simulation_type = 'UMP';
SELECT parameter_name,
parameter_value
FROM ahl_parameters
WHERE parameter_source_type = 'FLT'
AND parameter_source_sub_type = 'FUHUC';
SELECT parameter_name,
parameter_value
FROM ahl_parameters
WHERE parameter_source_type = 'FLT'
AND parameter_source_sub_type = 'FUHSC';
SELECT decode((SUM(unit_per_day)/(p_end_date - p_start_date + 1)), null, 0, (SUM(unit_per_day)/(p_end_date - p_start_date + 1))) unit_per_month
FROM
(SELECT ddate,
COUNT(*) unit_per_day
FROM
(SELECT rownum,
(p_start_date + rownum - 1) ddate
FROM fnd_new_messages
WHERE rownum <= (p_end_date - p_start_date + 1)
) dtab,
ahl_fleet_unit_assocs fleet
WHERE fleet.fleet_header_id = p_fleet_header_id
and fleet.simulation_plan_id = p_sim_plan_id
AND dtab.ddate BETWEEN association_start AND NVL(association_end, dtab.ddate)
GROUP BY ddate
);
SELECT unit_config_header_id
FROM ahl_fleet_unit_assocs
WHERE fleet_header_id = p_fleet_header_id
AND simulation_plan_id = p_sim_plan_id
AND (association_start BETWEEN p_start_date AND p_end_date
OR association_end BETWEEN p_start_date AND p_end_date
OR (association_start < p_start_date
AND association_end > p_end_date));
SELECT *
FROM
(SELECT ccr.net_reading
FROM CSI_COUNTER_ASSOCIATIONS csa,
ahl_unit_config_headers auch,
csi_counter_readings ccr,
ahl_fleet_unit_assocs afua,
csi_item_instances cii,
csi_counters_vl ccv
WHERE afua.unit_config_header_id = auch.unit_config_header_id
AND auch.csi_item_instance_id = cii.instance_id
AND ccv.counter_id = ccr.counter_id
AND csa.source_object_code = 'CP'
AND csa.source_object_id = auch.csi_item_instance_id
AND ccr.counter_id = csa.counter_id
AND afua.fleet_header_id = p_fleet_header_id
AND afua.unit_config_header_id = p_unit_config_header_id
AND ccv.counter_template_name = p_counter_name
AND ccv.uom_code = p_uom_code
AND ccr.value_timestamp <= p_counter_val_date
ORDER BY ccr.value_timestamp DESC
)
WHERE rownum < 2;
SELECT MIN(association_start) window_start,
MAX(NVL(association_end,sysdate)) window_end
FROM ahl_fleet_unit_assocs
WHERE fleet_header_id = p_fleet_header_id;
SELECT (sysdate+1) period_start_date,
MAX(NVL(period_end_date, AHL_UMP_ProcessUnit_PVT.Get_Rolling_Window_Date)) period_end_date
FROM ahl_fleet_utlzn_forecast
WHERE fleet_header_id = p_fleet_header_id;*/
SELECT (sysdate+1) period_start_date,
MAX(NVL(association_end, Get_Rolling_Window_Date)) period_end_date
FROM ahl_fleet_unit_assocs
WHERE fleet_header_id = p_fleet_header_id;*/
util_forecasts_table(l_record_num).LAST_UPDATE_DATE := SYSDATE;
util_forecasts_table(l_record_num).LAST_UPDATED_BY := FND_GLOBAL.user_id;
util_forecasts_table(l_record_num).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
util_forecasts_table(l_record_num).LAST_UPDATE_DATE := SYSDATE;
util_forecasts_table(l_record_num).LAST_UPDATED_BY := FND_GLOBAL.user_id;
util_forecasts_table(l_record_num).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
fnd_log.string (G_DEBUG_STMT, l_debug_module, ' utilization calculation over for all fleets. insertion of data into AHL_FLEET_UTILIZATION begins');
INSERT
INTO AHL_FLEET_UTILIZATION
(
FLEET_UTILIZATION_ID,
OBJECT_VERSION_NUMBER,
FLEET_HEADER_ID,
NAME,
OPERATING_ORG_ID,
PERIOD_START_DATE,
PERIOD_END_DATE,
UOM_CODE,
FLEET_SIZE,
FORECASTED_DAILY_USAGE,
FORECASTED_DAILY_SORTIES,
ACTUAL_DAILY_USAGE,
ACTUAL_DAILY_SORTIES,
SIMULATION_PLAN_ID,
SECURITY_GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
util_forecasts_table(x).FLEET_UTILIZATION_ID,
util_forecasts_table(x).object_version_number,
util_forecasts_table(x).FLEET_HEADER_ID,
util_forecasts_table(x).NAME,
util_forecasts_table(x).OPERATING_ORG_ID,
util_forecasts_table(x).PERIOD_START_DATE,
util_forecasts_table(x).PERIOD_END_DATE,
util_forecasts_table(x).UOM_CODE,
util_forecasts_table(x).FLEET_SIZE,
util_forecasts_table(x).FORECASTED_DAILY_USAGE,
util_forecasts_table(x).FORECASTED_DAILY_SORTIES,
util_forecasts_table(x).ACTUAL_DAILY_USAGE,
util_forecasts_table(x).ACTUAL_DAILY_SORTIES,
util_forecasts_table(x).SIMULATION_PLAN_ID,
util_forecasts_table(x).SECURITY_GROUP_ID,
util_forecasts_table(x).LAST_UPDATE_DATE,
util_forecasts_table(x).LAST_UPDATED_BY,
util_forecasts_table(x).CREATION_DATE,
util_forecasts_table(x).CREATED_BY,
util_forecasts_table(x).LAST_UPDATE_LOGIN,
util_forecasts_table(x).ATTRIBUTE_CATEGORY,
util_forecasts_table(x).ATTRIBUTE1,
util_forecasts_table(x).ATTRIBUTE2,
util_forecasts_table(x).ATTRIBUTE3,
util_forecasts_table(x).ATTRIBUTE4,
util_forecasts_table(x).ATTRIBUTE5,
util_forecasts_table(x).ATTRIBUTE6,
util_forecasts_table(x).ATTRIBUTE7,
util_forecasts_table(x).ATTRIBUTE8,
util_forecasts_table(x).ATTRIBUTE9,
util_forecasts_table(x).ATTRIBUTE10,
util_forecasts_table(x).ATTRIBUTE11,
util_forecasts_table(x).ATTRIBUTE12,
util_forecasts_table(x).ATTRIBUTE13,
util_forecasts_table(x).ATTRIBUTE14,
util_forecasts_table(x).ATTRIBUTE15
);
fnd_log.string (G_DEBUG_STMT, l_debug_module, ' insertion of data into AHL_FLEET_UTILIZATION ends');
util_forecasts_table.delete;
fnd_log.string (G_DEBUG_STMT, l_debug_module, ' util_forecasts_table deleted');
SELECT decode((SUM(unit_per_day)/(p_end_date - p_start_date + 1)), null, 0, (SUM(unit_per_day)/(p_end_date - p_start_date + 1))) unit_per_month
FROM
(SELECT ddate,
COUNT(*) unit_per_day
FROM
(SELECT rownum,
(p_start_date + rownum - 1) ddate
FROM fnd_new_messages
WHERE rownum <= (p_end_date - p_start_date + 1)
) dtab,
ahl_fleet_unit_assocs fleet
WHERE fleet.fleet_header_id = p_fleet_header_id
and fleet.simulation_plan_id = p_sim_plan_id
AND dtab.ddate BETWEEN association_start AND NVL(association_end, dtab.ddate)
GROUP BY ddate
);
SELECT association_start,
association_end,
(association_end - association_start + 1) assoc_days
FROM ahl_fleet_unit_assocs
WHERE fleet_header_id = p_fleet_header_id
AND simulation_plan_id = p_simulation_plan_id
AND ((association_start BETWEEN p_period_start AND p_period_end)
OR (association_end BETWEEN p_period_start AND p_period_end)
OR (p_period_start BETWEEN association_start AND association_end)
OR (p_period_end BETWEEN association_start AND association_end))
ORDER BY association_start,
association_end;
SELECT uom_code,
decode(sign(period_start_date - sysdate), 1, period_start_date, sysdate + 1) period_start_date,
period_end_date,
forecasted_daily_usage,
fleet_header_id
FROM AHL_FLEET_UTLZN_FORECAST
WHERE fleet_header_id = p_fleet_header_id
AND uom_code = p_uom_code
ORDER BY period_start_date;
/*SELECT FUF.uom_code, FUF.period_start_date, FUF.period_end_date,FUA.association_start ,FUA.association_end,
FUF.forecasted_daily_usage usage_per_day, FUA.fleet_header_id, FUA.unit_config_header_id, FUA.simulation_plan_id
FROM ahl_fleet_utlzn_forecast FUF ,ahl_fleet_unit_assocs FUA
WHERE trunc(nvl(period_end_date, sysdate)) >= trunc(sysdate)
AND trunc(nvl(association_end, sysdate)) >= trunc(sysdate)
AND trunc(nvl(period_end_date, association_start)) >= trunc(association_start)
AND FUA.simulation_plan_id = p_simulation_plan_id
AND FUA.fleet_header_id = p_fleet_header_id
--and FUA.unit_config_header_id = p_unit_config_header_id
order by uom_code, association_start, period_start_date;*/
SELECT rownum,
forecasted_daily_usage
FROM ahl_fleet_utlzn_forecast
WHERE fleet_header_id = p_fleet_header_id
AND UOM_CODE = p_sorties_counter_uom_code
AND ((period_start_date BETWEEN period_start AND period_end)
OR (period_end_date BETWEEN period_start AND period_end)
OR (period_start BETWEEN period_start_date AND period_end_date)
OR (period_end BETWEEN period_start_date AND period_end_date));