CREATE OR REPLACE FORCE VIEW EMRVWTODAYSTASKLISTCOUNT (EMR_TRANSACTION_ID, TASK_ID, TASK_NAME, APPOINTMENT_DATE, PATIENT_FIRSTNAME, PATIENT_LASTNAME, CONSULTANT_ID, ROLE_NAME, LOCATION_ID, ID_VALUE) AS
SELECT DISTINCT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
case when ET.TASK_ID IN (9,112) THEN TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY')
ELSE '1/1/1900' END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppDetailsSuppHistory EA ON
(
ET.ID_VALUE = EA.APPOINTMENT_ID and ET.TASK_ID IN (9,112)
OR
ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24,
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150)
)
WHERE ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24,
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150,9,112)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EED.CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID, ET.ID_VALUE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRTranscriptionMapping ETM, EMREncounterDetails EED,
EMRTranscriptionData ETD
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.ID_VALUE = ETM.TRANSCRIPTION_ID AND ETM.ENCOUNTER_ID = EED.ENCOUNTER_ID AND
ETD.IS_VERIFIED = 0 AND ETD.TRANSCRIPTION_ID = ETM.TRANSCRIPTION_ID AND EED.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 15
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
Case When Nvl(EED.APPOINTMENT_ID,'0')='0' then
'1/1/1900'
ELSE TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY')
END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRENCOUNTERDETAILS EED ON ET.ID_VALUE = EED.ENCOUNTER_ID
WHERE ET.TASK_ID IN (16,17,25,26,27,153)
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
'1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
WHERE ET.TASK_ID IN (34, 61, 62, 73, 109)
OR (ET.TASK_ID IN (124, 126, 147, 154) AND ET.ROLE_NAME IS NOT NULL)
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID
WHERE ET.TASK_ID IN (28)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
'1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,
CON.CONSULTANT_ID AS CONSULTANT_ID, ET.ROLE_NAME , AL.LOCATION_ID AS LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRTransactions ET inner join EMRTaskListLkup ETL on ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRDISEASEALERTS AL ON AL.DISEASE_ALERT_ID=ET.ID_VALUE
LEFT JOIN EMRCONSULTANTDETAILS CON ON ET.USER_LOGIN = CON.CONSULTANT_ID
WHERE ET.TASK_ID IN (122)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, TASK_NAME,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTransactions ET
INNER JOIN EMRTASKLISTLKUP ETL ON ET.TASK_ID = ETL.TASK_ID
INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID
INNER JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_STATUS_ID
WHERE ET.TASK_ID IN (1, 2, 3)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRACTransactions ET
INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN EMRPatientsMaster EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRENCOUNTERDETAILS EED ON EED.ENCOUNTER_ID = ET.ID_VALUE
INNER JOIN EMRCONSULTANTDETAILS ECD ON EED.CONSULTANT_ID = ECD.CONSULTANT_ID
LEFT OUTER JOIN EMRAppointmentDetailsHistory EA ON EED.APPOINTMENT_ID = EA.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
WHERE ET.TASK_ID IN (152)