SELECT DISTINCT WO_NUM, PCAR_ID, UNIT_MODL, UNIT_SN, UNIT_CODE, COMPONENT, WRK_CNTR, SMR_SAMP, CDR_TIME, CRN_ST, STATUS, WO_ASSIGNED, UPDATEDAT
FROM (SELECT
e.WO_NUM,
b.PCAR_ID,
a.UNIT_MODL,
a.UNIT_SN,
a.UNIT_CODE,
STUFF((SELECT DISTINCT ',' + CONVERT( VARCHAR(20),w.COMPONENT) FROM cbm.UNIT_CDTL_REF w, (SELECT z.HIST_COMP_REPL_COMPONENTS COMPONENT, z.MODEL
		FROM cbm.ABNORMAL x, cbm.[PARAMETER] y, cbm.REF_PAP_PART_COMP z
		WHERE x.PARM_ID = y.PARM_ID AND y.COMPONENT = z.PAP_COMPONENTS AND y.UNIT_MODL = z.MODEL AND x.PCAR_ID = b.PCAR_ID) x
		WHERE w.COMPONENT = x.COMPONENT AND w.UNIT_MODL = x.MODEL AND w.KEY_N = a.KEY_N FOR XML PATH('')),1,1,'') AS COMPONENT,
a.WRK_CNTR,
a.CDR_TIME,
b.CRN_ST,
c.DESCRIPTION STATUS,
(SELECT MAX(SMR_SAMP) from cbm.UNIT_CDTL_REF cdtlref WHERE
(cdtlref.KEY_N = b.KEY_N or cdtlref.KEY_N = b.KEY_N_PAP)
AND cdtlref.UNIT_MODL = e.UNIT_MODL
AND cdtlref.UNIT_SN = e.UNIT_SN
AND cdtlref.UNIT_CODE = e.UNIT_CODE
AND cdtlref.WRK_CNTR = e.ID_WRK_CNTR
) SMR_SAMP,
CASE WHEN (e.ISAPPROVEDALL IS NULL OR e.ISAPPROVEDALL = 0) THEN 0 ELSE 1 END AS ISAPPROVED,
CASE WHEN EXISTS(SELECT ID_HEADER FROM dca.PCAR_ACTN WHERE ID_HEADER = CAST(e.WO_NUM AS BIGINT) AND PCAR_ID = b.PCAR_ID) THEN 1
ELSE 0 END AS WO_ASSIGNED,
CAST(d.ST_DT AS DATE) UPDATEDAT
FROM cbm.UNIT_CDTL_REF a, cbm.PCAR b, cbm.REF_PCAR_ST c, cbm.PCAR_ST d, dca.HEADER e WHERE
b.CRN_ST = c.ST_ID
AND d.PCAR_ID = b.PCAR_ID
AND d.STATUS = b.CRN_ST
AND d.STATUS = c.ST_ID
AND b.CRN_ST > 1
AND a.UNIT_MODL = e.UNIT_MODL
AND a.UNIT_SN = e.UNIT_SN
AND a.UNIT_CODE = e.UNIT_CODE
AND a.WRK_CNTR = e.ID_WRK_CNTR
AND ((b.KEY_N IS NOT NULL AND a.KEY_N = b.KEY_N) OR (b.KEY_N IS NULL AND a.KEY_N = b.KEY_N_PAP))
) a WHERE ISAPPROVED = 0