CREATE OR replace VIEW @pcwschema@scannerdetails AS
    SELECT     scm.id                                AS scannermasterid,
               scm.guid                              AS guid,
               scm.transporttype                     AS transporttype,
               scm.serialnumber                      AS serialnumber,
               scm.modelnumber                       AS modelnumber,
               scm.speed                             AS speed,
               scm.numberofdroplets                  AS numberofdroplets,
               scm.endorsementlines                  AS endorsementlines,
               scm.classid                           AS classid,
               scm.createdate                        AS createdate,
               scm.lastmodifydate                    AS lastmodifydate,
               scm.siteid                            AS siteid,
               scm.numberofpockets                   AS numberofpockets,
               scm.lastcleaningdate                  AS lastcleaningdate,
               scm.workstationname                   AS workstationname,
               scm.macaddress                        AS macaddress,
               sc.customerid                         AS customerid,
               sce.name                              AS name,
               sce.enrollmentstate                   AS enrollmentstate,
               sce.registrationstate                 AS registrationstate,
               sc.lastdeposit                        AS lastdeposit,
               sc.status                             AS status,
               (
                          SELECT     utl_raw.cast_to_nvarchar2(listagg( (
                                     CASE
                                                WHEN count(slt.locationid)= 1 THEN( utl_raw.cast_to_raw(
                                                           (
                                                                      SELECT     loc.description
                                                                      FROM       @pcwschema@pcwlocation loc
                                                                      inner join @pcwschema@pcwscannerlocation slt
                                                                      ON         slt.locationid = loc.id
                                                                      WHERE      loc.id = slt.locationid
                                                                      GROUP BY   loc.description)) )
                                                ELSE( utl_raw.cast_to_raw(cast(count(slt.locationid)AS nvarchar2(80))) )
                                     END) ) within GROUP (ORDER BY 1))
                          FROM       @pcwschema@pcwscannerenroll sce
                          inner join @pcwschema@pcwscannerlocation slt
                          ON         sce.id = slt.scannerenrollid
                          WHERE      sce.id = slt.scannerenrollid
                          GROUP BY   1 ) AS scannerlocations
    FROM       @pcwschema@pcwscannermaster scm
    inner join @pcwschema@pcwscannerenroll sce
    ON         scm.id = sce.scannermasterid
    inner join @pcwschema@pcwscanner sc
    ON         scm.id = sc.scannermasterid
    WHERE      scm.id=sce.scannermasterid
    AND        scm.id=sc.scannermasterid;