SELECT
ProspectEmployer.tradingName,
ProspectEmployer.registeredName,
ProspectEmployer.primaryAlternateID,
ProspectEmployer.concernRoleID,
ProspectEmployer.employerConcernRoleID,
ConcernRole.sensitivity,
city.elementValue,
addressLine1.elementValue
INTO
:tradingName,
:registeredName,
:primaryAlternateID,
:concernRoleID,
:employerConcernRoleID,
:sensitivity,
:city,
:addressLine1
FROM
ProspectEmployer,
ConcernRole,
AddressElement city,
AddressElement addressLine1
WHERE ConcernRole.concernRoleID IN
(
SELECT
concernRoleID
FROM
concernRoleAlternateID
WHERE concernRoleAlternateID.alternateID = :primaryAlternateID
)
AND ProspectEmployer.concernRoleID = ConcernRole.concernRoleID
AND
(
city.addressID = ConcernRole.primaryAddressID
AND city.elementType = :cityType
)
AND
(
addressLine1.addressID = ConcernRole.primaryAddressID
AND addressLine1.elementType = :addressLine1Type
)
|