CREATE view reporte_citas as 
 SELECT room_appointment_study_receipt.id_room_appointment_study_receipt AS id_cita,
    room_appointment_study_receipt.validate_datetime AS fecha_validacion,
    user_val.username AS usuario_validacion,
    appointment.appointment_date AS fecha_cita,
    eps.name AS nombre_eps,
    appointment.appointment_status AS estado,
    appointment.datetime_asistencial AS fecha_asistencial,
    user_asistencial.username AS usuario_asistencial,
    provenance_date.name AS origen,
    priority.name AS prioridad,
    user_receipt.username AS usuario_recepcion,
    internal_order.doctor_referring AS medico_remitente,
    internal_order.document_doctor_referring AS documento_medico_remitente,
    internal_order.codigo_cie10,
    cie_10.nombre_cie_10 AS nombre_cie10,
    patient.contact_name AS nombre_acudiente,
    patient.contact_telephone AS telefono_acudiente,
    patient.weight AS peso_paciente,
    municipality.municipality_name AS municipio_paciente,
    department.department_name AS departamento_paciente,
    person_data.id_document_type AS tipo_documento_paciente,
    person_data.document AS documento_paciente,
    person_data.first_name AS primer_nombre_paciente,
    person_data.second_name AS segundo_nombre_paciente,
    person_data.first_last_name AS primer_apellido_paciente,
    person_data.second_last_name AS segundo_apellido_paciente,
    person_data.date_birth AS fecha_nacimiento_paciente,
    person_data.telephone AS telefono_paciente,
    person_data.email AS email_paciente,
    person_data.cellphone AS celular_paciente,
    person_data.address AS direccion_paciente,
    person_data.sex AS sexo_paciente,
    asignacion_cita.username AS usuario_asignacion_cita,
    asignacion_cita.current AS fecha_asignacion_cita,
    asignacion_cita.description AS comentario_asignacion_cita,
    confirmacion_cita.username AS usuario_confirmacion_cita,
    confirmacion_cita.current AS fecha_confirmacion_cita,
    confirmacion_cita.description AS comentario_confirmacion_cita,
    cancelacion_cita.username AS usuario_cancelacion_cita,
    cancelacion_cita.current AS fecha_cancelacion_cita,
    cancelacion_cita.description AS comentario_cancelacion_cita,
    transcription.current AS fecha_transcripcion,
    user_transcripcion.username AS usuario_transcripcion,
    room.id_modality AS modalidad,
    room.room_name AS nombre_sala,
    headquarter.headquarter_name AS nombre_sede,
    plates_registry.plates_good AS placas_buenas,
    plates_registry.plates_bad AS placas_malas,
    plates_registry.number_exposures AS numero_disparos,
    plates_registry.over_exposure AS "sobre_exposición",
    plates_registry.reason_overexposure AS motivo_sobre_exposicion,
    plates_registry.amount_mas AS mas,
    plates_registry.amount_ma AS ma,
    plates_registry.amount_kva AS kva,
    plates_registry.amount_mgy AS mgy,
    plates_registry.weight AS peso_asistencial,
    tecnologo.username AS tecnologo
   FROM room_appointment_study_receipt
     LEFT JOIN user_sonata user_val ON user_val.id = room_appointment_study_receipt.id_user_ras
     JOIN appointment USING (appointment_code)
     LEFT JOIN eps USING (id_eps)
     LEFT JOIN user_sonata user_asistencial ON appointment.id_user_assistencial = user_asistencial.id
     LEFT JOIN provenance_date ON provenance_date.id = appointment.id_provenance_date
     LEFT JOIN priority ON priority.id = appointment.id_priority
     LEFT JOIN user_sonata user_receipt ON appointment.id_user_receipt = user_receipt.id
     JOIN internal_order USING (internal_order_code)
     LEFT JOIN cie_10 ON internal_order.codigo_cie10::text = cie_10.codigo_cie_10::text
     JOIN patient USING (id_patient)
     JOIN person_data USING (id_person)
     LEFT JOIN municipality USING (municipality_code)
     LEFT JOIN department USING (department_code)
     LEFT JOIN ( SELECT schedule_logs.current,
            schedule_logs.description,
            user_sonata.username,
            schedule_logs.appointment_code
           FROM schedule_logs
             JOIN user_sonata ON user_sonata.id = schedule_logs.id_user
          WHERE schedule_logs.action::text = 'ASIGNACION'::text) asignacion_cita USING (appointment_code)
     LEFT JOIN ( SELECT schedule_logs.current,
            schedule_logs.description,
            user_sonata.username,
            schedule_logs.appointment_code
           FROM schedule_logs
             JOIN user_sonata ON user_sonata.id = schedule_logs.id_user
          WHERE schedule_logs.action::text = 'CONFIRMACION'::text) confirmacion_cita USING (appointment_code)
     LEFT JOIN ( SELECT schedule_logs.current,
            schedule_logs.description,
            user_sonata.username,
            schedule_logs.appointment_code
           FROM schedule_logs
             JOIN user_sonata ON user_sonata.id = schedule_logs.id_user
          WHERE schedule_logs.action::text = 'CANCELACION'::text) cancelacion_cita USING (appointment_code)
     LEFT JOIN transcription USING (id_room_appointment_study_receipt)
     LEFT JOIN user_sonata user_transcripcion ON transcription.id_user = user_transcripcion.id
     JOIN room USING (room_code)
     JOIN headquarter headquarter(id_headquarter, address, telephone, municipality_code_1, id_entity, current, headquarter_name, contacto, document, email, enabling_code, effective_activation_code, commercial_registration, id_flux_type, enabled, prefix, id_document_type, integration_his, codigo_ips, url_web_service) USING (id_headquarter)
     LEFT JOIN plates_registry plates_registry(id_registry, plates_good, plates_bad, number_exposures, number_unneccesary_exposures, amount_mas, amount_kva, id_person_1, appointment_code, current, amount_mgy, snapnumber, prevstudies, over_exposure, number_plates, number_studies, id_user_tecno, size_plates, biopsy, amount_ma, amount_compresion, amount_mgyagd, amount_ctdi, amount_dlp, weight, amount_mci, amount_qradiof, amount_imgcine, amount_imgfluoro, amount_dapugy, amount_cumulate_dose, amount_time_fluo, reason_overexposure) USING (appointment_code)
     LEFT JOIN user_sonata tecnologo ON plates_registry.id_user_tecno = tecnologo.id;

    create view reporte_estudios as 
      SELECT study.id_room_appointment_study_receipt AS id_cita,
    study.study_name AS nombre_estudio,
    study.cups,
    study.id_modality AS modalidad,
    study.clinical AS datos_clinicos
   FROM study
     JOIN study_list USING (id_study_list);
