Immunizations

select
    pi.id as "IMMUNIZATION_ID"
  , pi.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob
  , pi.cvx
  , pi.name as "VACCINE_NAME"
  , pi.administered_date
  , pi.qty
  , pi.qty_units
  , pi.lot_number
  , pi.manufacturer_name
  , pi.method
  , pi.site
  , pi.reason
  , pi.notes
  , concat('(', pi.administering_physician_id, ') ', a_user.first_name, ' ', a_user.last_name) as "ADMINISTERING_PHYSICIAN"
  , concat('(', pi.ordering_physician_id, ') ', o_user.first_name, ' ', o_user.last_name) as "ORDERING_PHYSICIAN"
  , pi.creation_time
  , pi.deletion_time
  , pi.last_modified
from patient_immunization pi
  join patient pt on pt.id = pi.patient_id
  left join user a_user on a_user.physician_id = pi.administering_physician_id
  left join user o_user on o_user.physician_id = pi.ordering_physician_id
where pi.deleted_by_user_id is null;

select
    pid.id as "IMMUNIZATION_DECLINED_ID"
  , pid.patient_id
  , pt.first_name as "PATIENT_FIRST_NAME"
  , pt.last_name as "PATIENT_LAST_NAME"
  , pt.dob
  , pid.cdc_type
  , pid.declined_date
  , pid.declined_reason
  , case
      when pid.immunity = true then 'Existing Immunity'
      when pid.declined_reason = '00' then 'Parental decision'
      when pid.declined_reason = '01' then 'Religious exemption'
      when pid.declined_reason = '02' then 'Other'
      when pid.declined_reason = '03' then 'Patient decision'
      else 'Unknown'
    end as "DECLINED_REASON_LABEL"
  , pid.immunity
  , pid.creation_time
  , pid.deletion_time
  , pid.last_modified
from patient_immunization_declined pid
  join patient pt on pt.id = pid.patient_id
where pid.deleted_by_user_id is null;