Untitled

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `dragonfiatlas`.`vwCmsChangeRequestList` AS
select
    `RHdr`.`Id` AS `Id`,
    `RHdr`.`Id` AS `RequestHdrId`,
    `RHdr`.`RequestCode` AS `RequestCode`,
    `C`.`Id` AS `ClientId`,
    `RHdr`.`DateEntry` AS `DateRequested`,
    `C`.`ClientCode` AS `ClientCode`,
    `AT`.`AccountType` AS `AccountType`,
    concat(`dragonfiatlas`.`RPI`.`LastName`, ', ', `dragonfiatlas`.`RPI`.`FirstName`, ' ', ifnull(`dragonfiatlas`.`RPI`.`MiddleName`, '')) AS `Primary`,
    concat(`dragonfiatlas`.`RPI2`.`LastName`, ', ', `dragonfiatlas`.`RPI2`.`FirstName`, ' ', ifnull(`dragonfiatlas`.`RPI2`.`MiddleName`, '')) AS `Secondary`,
    (case
        when (`C`.`IsActive` = 1) then 'Active'
        else 'InActive'
    end) AS `Status`,
    `CS`.`CmsStatus` AS `RequestStatus`,
    `RHdr`.`DateEntry` AS `DateEntry`,
    `RHdr`.`IsDeleted` AS `IsDeleted`
from
    (((((`dragonfiatlas`.`RequestHdr` `RHdr`
join `dragonfiatlas`.`CmsStatuses` `CS` on
    (((`CS`.`Id` = `RHdr`.`CmsStatusId`)
        and (`CS`.`IsDeleted` = 0))))
join `dragonfiatlas`.`Clients` `C` on
    (((`C`.`Id` = `RHdr`.`ClientId`)
        and (`C`.`IsDeleted` = 0))))
join `commonreference`.`AccountTypes` `AT` on
    (((`AT`.`Id` = `C`.`AccountTypeId`)
        and (`AT`.`IsDeleted` = 0))))
join `dragonfiatlas`.`vwCmsClientInfo` `RPI` on
    (((`dragonfiatlas`.`RPI`.`ClientId` = `C`.`Id`)
        and (`dragonfiatlas`.`RPI`.`IsPrimary` = 1)
            and (`dragonfiatlas`.`RPI`.`IsDeleted` = 0))))
left join `dragonfiatlas`.`vwCmsClientInfo` `RPI2` on
    (((`dragonfiatlas`.`RPI2`.`ClientId` = `C`.`Id`)
        and (`dragonfiatlas`.`RPI2`.`IsPrimary` = 0)
            and (`dragonfiatlas`.`RPI2`.`IsDeleted` = 0))))
where
    ((`RHdr`.`RequestTypeId` = 2)
        and (`RHdr`.`IsDeleted` = 0)
            and (`RHdr`.`IsActive` <> 0));