SELECT DISTINCT cmtl.INBOUND_MATERIAL_TYPE_ID AS materialTypeId, (con.ID) AS containerID, con.BARCODE AS stagingID, inb_parent.BARCODE AS inboundID, pct.NAME AS containerType, FORMAT(con.LAST_SCANNED_DATE, 'dd-MMM-yyyy', 'en-US') AS lastScannedDateStr, FORMAT(con.CREATE_DATE, 'dd-MMM-yyyy', 'en-US') AS createDateStr, sub.materialName AS materialName, sub.materialId AS materiaTypeIds, locationPath.locationName AS locationName, ccte.NAME AS externalCondition, ccti.NAME AS internalCondition, ccte.NOTE_REQUIRED_FLG AS externalCondNoteRequired, ccti.NOTE_REQUIRED_FLG AS internalCondNoteRequired, con.INTERNAL_CONDITION_ID AS intConditionId, con.EXTERNAL_CONDITION_ID AS extConditionId, con.TRACKING_NUMBER AS trackingNumber, ccte.ID AS externalConditionTypeId, ccti.ID AS internalConditionTypeId, acc.ID AS accountId, acc.ACCOUNT_CODE AS accountCode, con.TEMPERATURE_LOGGER_ID AS temperatureLoggerID, ISNULL(con.SHIPPING_CONDITION, '-') AS shippingCondition, dbo.GROUP_CONCAT(sp.NAME) AS specialProcessing, procct.ID AS processingContainerTypeId, m.PRIMARYID AS studyId, con.ALTERNATE_BARCODE AS alternateBarcode, con.ALTERNATE_BOX_ID AS alternateBoxId, IIF(sub1.empty = 'Y', 'Yes', 'No') AS empty, COUNT(note2.ID) AS notes, cont.CONTACT_NAME AS createdBy, wr.BARCODE AS workRequestBarcode, (SELECT dbo.GROUP_CONCAT_DS(cspl.special_processing_id, ';', 1) FROM CONTAINER_SPEC_PROC_LNK cspl WHERE cspl.container_id = con.id) AS specProcIdList FROM container con INNER JOIN CONTAINER_MAT_TYPE_LINK cmtl ON cmtl.CONTAINER_ID = con.ID INNER JOIN CONTAINER con_parent ON con_parent.ID = con.PARENT_CONTAINER_ID INNER JOIN physical_container_type pct ON con.physical_container_type_id = pct.id INNER JOIN PROCESSING_CONTAINER_TYPE procct ON con.PROCESSING_CONTAINER_TYPE_ID = procct.id INNER JOIN material_type matType ON cmtl.inbound_material_type_id = matType.id INNER JOIN eap_work_request wr ON wr.id = con.work_request_id INNER JOIN wr_task task ON task.work_request_id = wr.id INNER JOIN wr_subtask subtask ON subtask.task_id = task.id LEFT JOIN CONTAINER_SPEC_PROC_LNK cspl ON con.id = cspl.CONTAINER_ID LEFT JOIN SPECIAL_PROCESSING sp ON cspl.SPECIAL_PROCESSING_ID = sp.ID LEFT JOIN CONTAINER_CONDITION cci ON con.INTERNAL_CONDITION_ID = cci.ID LEFT JOIN CONTAINER_CONDITION_TYPE ccti ON cci.CONDITION_TYPE_ID = ccti.ID LEFT JOIN CONTAINER_CONDITION cce ON con_parent.EXTERNAL_CONDITION_ID = cce.ID LEFT JOIN CONTAINER_CONDITION_TYPE ccte ON cce.CONDITION_TYPE_ID = ccte.ID LEFT JOIN EAP_USER eu ON eu.id = con.A_CREATE_BY LEFT JOIN EAP_CONTACTS cont ON cont.id = eu.CONTACT_ID LEFT JOIN LOCATION loc ON con.LOCATION_ID = loc.ID CROSS APPLY ( SELECT dbo.GROUP_CONCAT_D(lPath.name, '-') AS locationName FROM LOCATION lPath WHERE loc.hid.IsDescendantOf(lPath.hid) = 1 ) AS locationPath LEFT JOIN EAM_ACCOUNT acc ON wr.account_id = acc.id LEFT JOIN material_transfer_container mtc ON mtc.container_id = con.id LEFT JOIN CONTAINER_METAINDEX_LINK cml ON cml.CONTAINER_ID = con.ID LEFT JOIN METAINDEX m ON m.ID = cml.METAINDEX_ID LEFT JOIN NOTE2 note2 ON note2.PARENT_ID = con.ID AND note2.PARENT_TABLE = 'CONTAINER' INNER JOIN ( SELECT conSub.id AS contID ,dbo.GROUP_CONCAT_D(mt.NAME, ', ') AS materialName ,dbo.GROUP_CONCAT_D(mt.ID, ',') AS materialId FROM CONTAINER conSub LEFT JOIN CONTAINER_MAT_TYPE_LINK cmtl ON conSub.ID = cmtl.CONTAINER_ID INNER JOIN MATERIAL_TYPE mt ON mt.ID = cmtl.INBOUND_MATERIAL_TYPE_ID WHERE mt.id = cmtl.INBOUND_MATERIAL_TYPE_ID GROUP BY conSub.id ) sub ON sub.contID = con.ID INNER JOIN CONTAINER inb_parent ON con.hid.IsDescendantOf(inb_parent.HID) = 1 AND inb_parent.PROCESSING_CONTAINER_TYPE_ID = ? LEFT JOIN ( SELECT conSub1.id AS contID ,cmtl.IS_EMPTY AS empty FROM CONTAINER conSub1 LEFT JOIN CONTAINER_MAT_TYPE_LINK cmtl ON conSub1.ID = cmtl.CONTAINER_ID INNER JOIN MATERIAL_TYPE mt ON mt.ID = cmtl.INBOUND_MATERIAL_TYPE_ID WHERE mt.id = cmtl.INBOUND_MATERIAL_TYPE_ID AND cmtl.INBOUND_MATERIAL_TYPE_ID = ? GROUP BY conSub1.id ,cmtl.IS_EMPTY ) sub1 ON sub1.contID = con.ID WHERE cmtl.INBOUND_MATERIAL_TYPE_ID = ? AND con.id IN ( ? , ? ) GROUP BY cmtl.INBOUND_MATERIAL_TYPE_ID, con.id, locationPath.locationName, acc.account_code, acc.id, con.barcode, pct.name, con.last_scanned_date, matType.name, inb_parent.barcode, con.create_date, ccte.NAME, ccti.NAME, con.temperature_logger_id, con.shipping_condition, ccte.NOTE_REQUIRED_FLG, ccti.NOTE_REQUIRED_FLG, con.INTERNAL_CONDITION_ID, con.EXTERNAL_CONDITION_ID, con.TRACKING_NUMBER, ccte.ID, ccti.ID, procct.ID, m.PRIMARYID, con.ALTERNATE_BARCODE, wr.BARCODE, con.ALTERNATE_BOX_ID, cmtl.IS_EMPTY, cont.CONTACT_NAME, sub.materialName, sub.materialId, sub1.empty