set search_path=gbitcontract;
create or replace view driverappincidentdetails
as
select
  "DI_PK" as pk,
  "DI_HD_PK" as driverpk,
  "HD_DISPLAY_NAME" as driver,
  fx."HF_FIRST_FB_NUMBER" as fixing,
  fx."HF_PLANNING_DAY" AS "SheduledDate",
    fx."HF_LOCN" AS "BaseSite",
    gb1_haul_jobs."HJ_JOB_DESC" AS "JobDetails",
    gb1_haul_jobs."HJ_POSTCODE" AS "JobPostcode",
    ((((
        CASE
            WHEN COALESCE(ca."NAME", ''::character varying)::text = ''::text THEN ''::character varying
            ELSE ca."NAME"
        END::text ||
        CASE
            WHEN COALESCE(ca."ADDRESS_LINE1", ''::character varying)::text = ''::text THEN ''::text
            ELSE E'\n' || ca."ADDRESS_LINE1"::text
        END) ||
        CASE
            WHEN COALESCE(ca."ADDRESS_LINE2", ''::character varying)::text = ''::text THEN ''::text
            ELSE E'\n' || ca."ADDRESS_LINE2"::text
        END) ||
        CASE
            WHEN COALESCE(ca."ADDRESS_LINE3", ''::character varying)::text = ''::text THEN ''::text
            ELSE E'\n' || ca."ADDRESS_LINE3"::text
        END) ||
        CASE
            WHEN COALESCE(ca."ADDRESS_LINE4", ''::character varying)::text = ''::text THEN ''::text
            ELSE E'\n' || ca."ADDRESS_LINE4"::text
        END) ||
        CASE
            WHEN COALESCE(ca."POSTCODE", ''::character varying)::text = ''::text THEN ''::text
            ELSE E'\n' || ca."POSTCODE"::text
        END AS "JobFullAdddress",
    fx."HF_SKIP_TYPE" AS "BinType",
  "DI_DATE_TIME" as incidentdate,
  "DI_COMMENTS" as incidentcomments,
  "DI_INCIDENT_TYPE" as incidenttype,
  CASE
	WHEN "DI_ABORT_JOB" = 'Y' THEN 'Yes'
	ELSE NULL
  END as jobaborted
 from gb1_haul_driver, gb1_haul_driver_incident_report
 left join gb1_haul_fixings as fx on fx."HF_FIRST_FB_NUMBER" = "DI_FB_NUMBER"
 left join gb1_haul_jobs on "HJ_PK" = "HF_HJ_PK"
 LEFT JOIN gbitcontract.gb1_delivery_address ca ON gb1_haul_jobs."HJ_FIXING_COLLECTION_ADDRESS_CODE" = ca."DELIVERY_CODE"
 where  "DI_HD_PK" = "HD_PK";


ALTER TABLE driverappincidentdetails
  OWNER TO gbitdeskapp;