--drop procedure QMgrGetQueueMessages_sel_sp

CREATE PROCEDURE [QMgrGetQueueMessages_sel_sp]
(
    @ContactType char(3),
    @ComContactType char(3)
)
AS

Set transaction isolation level read uncommitted

SELECT    a.TrxOutQueueID as TrxOutQueueID,
    f.TrxId,
    convert(char, e.StartTime) as UpdateTime,
    a.ContactType AS ContactType,
    a.ComContactId AS ContactID,
    a.ComType AS ComType,
    a.ComFreqType AS ComFreqType,
       a.ComTime AS ComTime,
    coalesce(a.ComMessage, ' ') AS Message,
       b.ContactID AS Account,
    b.PhoneFax AS FaxNumber,
    coalesce(b.Email, ' ') AS Email,
       (RTrim(b.LastName) + ', ' + RTrim(b.FirstName)) AS ToName,
    b.PhoneBeeper as BeeperNumber,
       b.PagerAlpha as Alpha,
    b.PagerTextLen as TextLength,
    coalesce(a.PageNumeric, ' ') as PageNumeric,
    coalesce(b.PagerPIN, ' ') as  PagerPIN,
       coalesce(c.PageEMail, ' ') as  PageEMail,
    coalesce(b.PagerEMail, ' ') as  PagerEMail,
    coalesce(c.PagePINDelay, 5) as  PagePINDelay,
    coalesce(c.PageMsgDelay, 5) as  PageMsgDelay,
       coalesce(c.PageCompanyName, ' ') as PageCompanyName,
    coalesce(a.PageAlpha, ' ') as PageAlpha,
    e.LocationID as LocationId,
    d.TransTypeDesc as TransTypeDesc,
      a.TrxDetailId as TrxDetailId,
     coalesce(CodesLocation.GMTToLocal, 0) as LocationHour,
    coalesce(c.PageCompanyCode, ' ') as PageCompanyCode,
      coalesce(CodesLocation.LocationFaxNumber, ' ') as LocationFaxNumber,
    a.ClassRegistrationId,
    CodesLocation.LocationFaxFormNumber,  'Y' as FaxTedFlag,
    coalesce(a.sendFlag,'Y') as sendFlag ,   f.FacilityId
into    #messages
FROM     TrxQueueOut a WITH (NOLOCK)
    INNER JOIN Contacts b WITH (NOLOCK) ON a.ComContactId = b.ContactId
        AND (a.ContactType  = @ContactType) 
    INNER JOIN TrxDetail e WITH (NOLOCK) on a.TrxId = e.TrxId and a.TrxDetailId = e.TrxDetailId
    INNER join Trx f WITH (NOLOCK) on f.TrxId = e.TrxId and f.TrxStatus = 'CLO'
    INNER JOIN TransType d WITH (NOLOCK) on d.TransTypeId = e.TransTypeId
       LEFT JOIN PagingCompany c WITH (NOLOCK) on b.PageCompanyID = c.PageCompanyID
      INNER Join CodesLocation WITH (NOLOCK) on CodesLocation.LocationID = e.LocationID
where     UpdateTime > GetDate()-4
and      a.QueueStatus in ('INP','QUE')
and     a.ComType in ('FAX','PRI','EMU','MAL','RRL')
and      ComContactType = @ComContactType
and       (comfreqtype in ('IMD','ANT')
    or
        (comfreqtype = 'HRY'
        and (datepart(mi,getdate()) between 54 and 59 or datepart(mi,getdate()) between 0 and 5))
       or     (comfreqtype = 'SPC'
        and (convert(char(5),getdate(),114) between convert(char(5), DateAdd(mi, -5, comtime), 114)  and convert(char(5), DateAdd(mi, 5, comtime), 114))))

update TrxQueueOut
set QueueStatus = 'INP'
where TrxOutQueueID in (select TrxOutQueueID from #messages)

select * from #messages
order by TrxId, ContactId, ComType, ContactType
GO