[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
events.event_id,
events.event_type,
events.event_start_time,
events.event_end_time
FROM
(
select
unit_schedule_id event_id,
G_EVENT_TYPE_FLIGHT event_type,
nvl(actual_departure_time, est_departure_time) event_start_time,
nvl(actual_arrival_time, est_arrival_time) event_end_time
from ahl_unit_schedules
where unit_config_header_id = p_unit_config_id
and
(
(
p_start_time <= nvl(actual_departure_time, est_departure_time) and
nvl(actual_departure_time, est_departure_time) < p_end_time
)
or
(
p_start_time < nvl(actual_arrival_time, est_arrival_time) and
nvl(actual_arrival_time, est_arrival_time) <= p_end_time
)
or
(
nvl(actual_departure_time, est_departure_time) <= p_start_time and
p_end_time <= nvl(actual_arrival_time, est_arrival_time)
)
)
UNION ALL
select
vwp.visit_id event_id,
G_EVENT_TYPE_VISIT event_type,
vwp.start_date_time event_start_time,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE) event_end_time
from ahl_visits_b vwp, ahl_unit_config_headers uc
where uc.unit_config_header_id = p_unit_config_id
and vwp.item_instance_id = uc.csi_item_instance_id
--priyan Bug # 5303188
and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE')
--and uc.unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
and vwp.status_code NOT IN ('CANCELLED', 'DELETED')
and vwp.start_date_time IS NOT NULL
and
(
(
p_start_time <= vwp.start_date_time and
vwp.start_date_time < p_end_time
)
or
(
p_start_time < nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) and
nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) <= p_end_time
)
or
(
vwp.start_date_time <= p_start_time and
p_end_time <= nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 )
)
)
) events
order by event_start_time asc, NVL(event_end_time, event_start_time + 1/1440) asc, event_type desc;
SELECT
event_id,
event_type,
event_start_time,
event_end_time
FROM
(
select
unit_schedule_id event_id,
G_EVENT_TYPE_FLIGHT event_type,
est_departure_time event_start_time,
est_arrival_time event_end_time
from ahl_unit_schedules
where unit_config_header_id = p_unit_config_id
and
(
(
p_start_time <= est_departure_time and
est_departure_time < p_end_time
)
or
(
p_start_time < est_arrival_time and
est_arrival_time <= p_end_time
)
or
(
est_departure_time <= p_start_time and
p_end_time <= est_arrival_time
)
)
UNION ALL
select
vwp.visit_id event_id,
G_EVENT_TYPE_VISIT event_type,
vwp.start_date_time event_start_time,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE) event_end_time
from ahl_visits_b vwp, ahl_unit_config_headers uc
where uc.unit_config_header_id = p_unit_config_id
and vwp.item_instance_id = uc.csi_item_instance_id
--priyan Bug # 5303188
and ahl_util_uc_pkg.get_uc_status_code (p_unit_config_id) IN ('COMPLETE', 'INCOMPLETE')
--and uc.unit_config_status_code IN ('COMPLETE', 'INCOMPLETE')
and vwp.status_code NOT IN ('CANCELLED', 'DELETED')
and vwp.start_date_time IS NOT NULL
and
(
(
p_start_time <= vwp.start_date_time and
vwp.start_date_time < p_end_time
)
or
(
p_start_time < nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) and
nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 ) <= p_end_time
)
or
(
vwp.start_date_time <= p_start_time and
p_end_time <= nvl(AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(vwp.VISIT_ID , FND_API.G_FALSE), vwp.start_date_time + 1/1440 )
)
)
)
order by event_start_time asc, NVL(event_end_time, event_start_time + 1/1440) asc, event_type desc;
select *
from ahl_unit_schedules_v
where unit_config_header_id = p_unit_config_id
--Modifying to capture preceding overlapping flights too...
--and nvl(actual_arrival_time, est_arrival_time) <= p_start_time
--order by nvl(actual_arrival_time, est_arrival_time) desc;
select *
from ahl_unit_schedules_v
where unit_config_header_id = p_unit_config_id
--Modifying to capture preceding overlapping flights too...
--and est_arrival_time <= p_start_time
--order by est_arrival_time desc;
SELECT VISIT_ID,
START_DATE_TIME
FROM (
SELECT
RANK () OVER (order by VB.START_DATE_TIME DESC) rank_value,
VB.VISIT_ID,
VB.START_DATE_TIME
FROM AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC
WHERE
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
VB.START_DATE_TIME < c_start_time AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
VB.START_DATE_TIME IS NOT NULL
)a1
WHERE a1.rank_value=1;
SELECT
VB.VISIT_ID,
VB.VISIT_NUMBER,
VB.OBJECT_VERSION_NUMBER,
VB.ORGANIZATION_ID,
HROU.NAME ORGANIZATION_NAME,
VB.DEPARTMENT_ID,
BDPT.DESCRIPTION DEPARTMENT_NAME,
VB.SERVICE_REQUEST_ID,
VB.START_DATE_TIME,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VB.VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
VB.STATUS_CODE,
VB.VISIT_TYPE_CODE,
VB.PROJECT_FLAG,
VB.PROJECT_ID,
VB.PROJECT_TEMPLATE_ID,
VB.ATTRIBUTE_CATEGORY,
VB.ATTRIBUTE1,
VB.ATTRIBUTE2,
VB.ATTRIBUTE3,
VB.ATTRIBUTE4,
VB.ATTRIBUTE5,
VB.ATTRIBUTE6,
VB.ATTRIBUTE7,
VB.ATTRIBUTE8,
VB.ATTRIBUTE9,
VB.ATTRIBUTE10,
VB.ATTRIBUTE11,
VB.ATTRIBUTE12,
VB.ATTRIBUTE13,
VB.ATTRIBUTE14,
VB.ATTRIBUTE15,
VB.UNIT_SCHEDULE_ID
FROM AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC,
HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT
WHERE
VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
VB.START_DATE_TIME IS NOT NULL AND
--Modifying to capture preceding overlapping visits too...
--CLOSE_DATE_TIME <= c_start_time
--ORDER BY CLOSE_DATE_TIME DESC;
SELECT a1.* FROM
(
SELECT
ORGANIZATION_NAME,
DEPARTMENT_NAME,
VISIT_ID,
VISIT_NUMBER,
OBJECT_VERSION_NUMBER,
ORGANIZATION_ID,
DEPARTMENT_ID,
SERVICE_REQUEST_ID,
START_DATE_TIME,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
STATUS_CODE,
VISIT_TYPE_CODE,
PROJECT_FLAG,
PROJECT_ID,
PROJECT_TEMPLATE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
UNIT_SCHEDULE_ID
FROM (
SELECT
HROU.NAME ORGANIZATION_NAME,
BDPT.DESCRIPTION DEPARTMENT_NAME,
VB.*
FROM
AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC,
HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT
WHERE
VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
START_DATE_TIME IS NOT NULL AND
VB.START_DATE_TIME = c_vst_start_time
) s1
)a1
WHERE
a1.CLOSE_DATE_TIME >= c_vst_end_date_time
ORDER BY NVL(CLOSE_DATE_TIME,START_DATE_TIME + 1/1440) DESC;
SELECT VISIT_ID,
START_DATE_TIME
FROM (
SELECT
RANK () OVER (order by VB.START_DATE_TIME ASC) rank_value,
VB.VISIT_ID,
VB.START_DATE_TIME
FROM AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC
WHERE
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
START_DATE_TIME > c_end_time AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
START_DATE_TIME IS NOT NULL
)a1
WHERE a1.rank_value=1;
SELECT
VB.VISIT_ID,
VB.VISIT_NUMBER,
VB.OBJECT_VERSION_NUMBER,
VB.ORGANIZATION_ID,
HROU.NAME ORGANIZATION_NAME,
VB.DEPARTMENT_ID,
BDPT.DESCRIPTION DEPARTMENT_NAME,
VB.SERVICE_REQUEST_ID,
VB.START_DATE_TIME,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VB.VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
VB.STATUS_CODE,
VB.VISIT_TYPE_CODE,
VB.PROJECT_FLAG,
VB.PROJECT_ID,
VB.PROJECT_TEMPLATE_ID,
VB.ATTRIBUTE_CATEGORY,
VB.ATTRIBUTE1,
VB.ATTRIBUTE2,
VB.ATTRIBUTE3,
VB.ATTRIBUTE4,
VB.ATTRIBUTE5,
VB.ATTRIBUTE6,
VB.ATTRIBUTE7,
VB.ATTRIBUTE8,
VB.ATTRIBUTE9,
VB.ATTRIBUTE10,
VB.ATTRIBUTE11,
VB.ATTRIBUTE12,
VB.ATTRIBUTE13,
VB.ATTRIBUTE14,
VB.ATTRIBUTE15,
VB.UNIT_SCHEDULE_ID
FROM AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC,
HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT
WHERE
VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
START_DATE_TIME > c_end_time AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
START_DATE_TIME IS NOT NULL
ORDER BY START_DATE_TIME ASC, NVL(CLOSE_DATE_TIME,START_DATE_TIME + 1/1440) ASC;*/
SELECT a1.* FROM
(
SELECT
ORGANIZATION_NAME,
DEPARTMENT_NAME,
VISIT_ID,
VISIT_NUMBER,
OBJECT_VERSION_NUMBER,
ORGANIZATION_ID,
DEPARTMENT_ID,
SERVICE_REQUEST_ID,
START_DATE_TIME,
AHL_VWP_TIMES_PVT.GET_VISIT_END_TIME(VISIT_ID , FND_API.G_FALSE) CLOSE_DATE_TIME,
STATUS_CODE,
VISIT_TYPE_CODE,
PROJECT_FLAG,
PROJECT_ID,
PROJECT_TEMPLATE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
UNIT_SCHEDULE_ID
FROM (
SELECT
HROU.NAME ORGANIZATION_NAME,
BDPT.DESCRIPTION DEPARTMENT_NAME,
VB.*
FROM
AHL_VISITS_B VB,
AHL_UNIT_CONFIG_HEADERS UC,
HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT
WHERE
VB.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+) AND
VB.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+) AND
VB.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID AND
UC.UNIT_CONFIG_HEADER_ID = c_unit_config_id AND
VB.STATUS_CODE NOT IN ('CANCELLED', 'DELETED') AND
START_DATE_TIME IS NOT NULL AND
VB.START_DATE_TIME = c_vst_start_time
) s1
)a1
WHERE
a1.CLOSE_DATE_TIME <= c_vst_end_date_time
ORDER BY NVL(CLOSE_DATE_TIME,START_DATE_TIME + 1/1440) ASC;
select *
from ahl_unit_schedules_v
where unit_config_header_id = p_unit_config_id
and nvl(actual_departure_time, est_departure_time) > p_end_time
order by nvl(actual_departure_time, est_departure_time) asc, nvl(actual_arrival_time, est_arrival_time) asc;
select *
from ahl_unit_schedules_v
where unit_config_header_id = p_unit_config_id
and est_departure_time > p_end_time
order by est_departure_time asc, est_arrival_time asc;