SELECT
placeID,
P.compartmentID,
P.name,
type,
startDate,
endDate,
startDateTime,
endDateTime,
P.recordStatus,
P.versionNo
INTO
:placeID,
:compartmentID,
:name,
:type,
:startDate,
:endDate,
:startDateTime,
:endDateTime,
:recordStatus,
:versionNo
FROM Place P, Compartment C
WHERE
P.compartmentID= C.compartmentID
AND (parentCompartmentID = :compartmentID OR C.compartmentID = :compartmentID)
AND P.recordStatus= :recordStatus
AND (P.endDate is NULL OR P.endDate >= :currentDate)
AND
placeid NOT IN
(
SELECT placeid
FROM outofuseperiod
WHERE
startDateTime <= :currentDateTime AND
(endDateTime is NULL OR endDateTime >= :currentDateTime)
)
AND
placeid NOT IN
(
SELECT placeid
FROM placement
WHERE
recordStatus= :recordStatus AND
startDate <= :currentDateTime AND
(endDate is NULL OR endDate >= :currentDateTime)
)
AND
placeid NOT IN
(
SELECT placeid
FROM reservation
WHERE
fromDate <= :currentDateTime AND
(toDate is NULL OR toDate>= :currentDateTime )
)
|