SELECT AsPolicy.PolicyNumber,
    AsTransaction.TransactionName,
    TO_CHAR( AsActivity.EffectiveDate, 'YYYY-MM-DD' ) EffectiveDate,
    StatusCode.ShortDescription Status,
    AsActivity.ActivityGuid
FROM
    AsActivity
    JOIN AsPolicy ON AsPolicy.PolicyGuid = AsActivity.PolicyGuid
    JOIN AsTransaction ON AsTransaction.TransactionGuid = AsActivity.TransactionGuid
    JOIN AsCode StatusCode ON StatusCode.CodeName = 'AsCodeStatus' AND StatusCode.CodeValue = AsActivity.StatusCode
WHERE
    AsActivity.TypeCode IN( '01', '04' )
    AND AsActivity.StatusCode IN( '01', '02', '09' )
    AND AsPolicy.PolicyNumber = '99250117318'
ORDER BY
    AsActivity.EffectiveDate DESC,
    AsActivity.ProcessingOrder DESC,
    AsActivity.EntryGmt DESC
