CREATE OR ALTER PROCEDURE [sSupport].[pCoboticsAHCList]
(
    @rOrgId SMALLINT
)
AS
BEGIN
    SET NOCOUNT ON;

  
        SELECT DISTINCT
            CASE WHEN PA.PatientSourceTypeId = 95 THEN ISNULL(EMP.Code, '-') WHEN PA.PatientSourceTypeId = 1009 THEN ISNULL(CSR.Code, '-') ELSE '-' END AS EmployeeCode,
            CASE WHEN PA.PatientSourceTypeId = 95 THEN ISNULL(EMP.FirstName + ' ' + EMP.LastName, '-') WHEN PA.PatientSourceTypeId = 1009 THEN ISNULL(CSR.FirstName + ' ' + CSR.LastName, '-') ELSE '-' END AS EmployeeName,
            Doctor.Code AS DoctorCode,
            ISNULL(Doctor.FirstName, '') + ' ' + ISNULL(Doctor.LastName, '') AS DoctorName,
            --ISNULL(CASE WHEN PA.CreatedDateTime IS NOT NULL THEN CONVERT(VARCHAR(10), PA.CreatedDateTime, 103) + ' ' + REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(30), PA.CreatedDateTime, 100), 8)), 'AM', ' AM'), 'PM', ' PM') END, '-') AS InTime,
            ISNULL(CASE WHEN PA.AssignDate IS NOT NULL THEN CONVERT(VARCHAR(10), PA.AssignDate, 103) + ' ' + REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(30), PA.AssignDate, 100), 8)), 'AM', ' AM'), 'PM', ' PM') END, '-') AS InTime,
            ISNULL(CASE WHEN LastOPD.OutTime IS NOT NULL THEN CONVERT(VARCHAR(10), LastOPD.OutTime, 103) + ' ' + REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(30), LastOPD.OutTime, 100), 8)), 'AM', ' AM'), 'PM', ' PM') END, '-') AS OutTime,
            SM.Title as HealthIndicatorStatus
        FROM
            [sSupport].[tProviderAppointment] PA
        LEFT JOIN
            [sHrm].[tEmployeeMaster] EMP ON EMP.Id = PA.PatientSourceId AND PA.PatientSourceTypeId = 95
        LEFT JOIN
            [sHrm].[tCsrBenefiterMaster] CSR ON CSR.Id = PA.PatientSourceId AND PA.PatientSourceTypeId = 1009
            INNER JOIN 
            [sCommon].[tStatusMaster] SM ON SM.Id= PA.HealthIndicatorId
        INNER JOIN
            [sHrm].[tEmployeeMaster] Doctor ON Doctor.Id = PA.AssignToDoctorId
        INNER JOIN
            [sExamination].[tSetupMaster] Setup ON Setup.Id = PA.SetupId
        INNER JOIN
            [sExamination].[tSetupGroupMaster] SGM ON SGM.Id = Setup.SetupGroupId AND SGM.IsProviderHealthCheckup = 1
        OUTER APPLY
        (
            SELECT TOP 1
                OPD.CreatedDateTime AS OutTime
            FROM
                [sSupport].[tProviderAppointmentLog] OPD
            WHERE
                OPD.ProviderAppointmentId = PA.Id AND OPD.StateId=591
            ORDER BY
                OPD.Id DESC
        ) LastOPD
        LEFT JOIN
            [sSupport].[tProviderAppointmentHealthIndicatorLog] Refer ON Refer.ProviderAppointmentId = PA.Id
        WHERE
            PA.OrgId = @rOrgId
        ORDER BY
            EmployeeCode,
            InTime;    
END;