SELECT 
    NULL AS Box,
    BUILTIN.DF(TransactionLine.Item) AS Item,
    NULL AS PO,
    'PHY' AS TEST,
    MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN CUSTOMRECORD_WMSSE_SKU_ALIAS.name END) AS masterbarcode,
    SUM(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS piecesperinner,
	(MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) / MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%BX%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END)) AS innerspercase,
    MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END) AS MasterQuantity,
    Transaction.TranID AS Shipmentnumber,
	MAX(TransactionLine.Quantity) / MAX(CASE WHEN CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_desc LIKE '%MC%' THEN REPLACE(REGEXP_SUBSTR(BUILTIN.DF(CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wms_alias_unit), '-\s*([0-9]+)', 1, 1, 'i'), '-','') END)  AS TotalMasterCount,
    MAX(TransactionLine.Quantity) AS Quantity, 
FROM 
    Transaction
    INNER JOIN TransactionLine ON (TransactionLine.Transaction = Transaction.ID)
    INNER JOIN Item ON (Item.ID = TransactionLine.Item)
    INNER JOIN CUSTOMRECORD_WMSSE_SKU_ALIAS ON (item.id = CUSTOMRECORD_WMSSE_SKU_ALIAS.custrecord_wmsse_alias_item)
WHERE 
    Transaction.Status = 'TrnfrOrd:F' AND TransactionLine.Quantity > 0 AND Transaction.TranID = 'TO110'
GROUP BY 
    Transaction.TranID, 
    BUILTIN.DF(Transaction.Type), 
    BUILTIN.DF(Transaction.Status), 
    BUILTIN.DF(TransactionLine.Item)
ORDER BY 
    BUILTIN.DF(TransactionLine.Item) ASC;