SELECT SUBSTRING(OD.PRODUCT_CODE, 4, 2)AS PRODUCT_SUB,
       OD.PRODUCT_CODE,
       OD.SHIP_MASTER_CUSTOMER_ID,
       C.LAST_NAME,
       C.FIRST_NAME,
       MAS.SHIP_MASTER_CUSTOMER_ID,
       ASOC.MASTER_CUSTOMER_ID AS ASOC_MASTER_CUSTOMER_ID

FROM   ORDER_DETAIL OD

INNER JOIN CUSTOMER C 
       ON (OD.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID)

LEFT JOIN (
       SELECT OD3.SHIP_MASTER_CUSTOMER_ID
       FROM ORDER_DETAIL OD3
       INNER JOIN PRODUCT P3 
              ON (OD3.PRODUCT_ID = P3.PRODUCT_ID)
       WHERE OD3.SUBSYSTEM = 'MBR'
       AND P3.PRODUCT_CLASS_CODE = 'DUES'
       AND OD3.LINE_STATUS_CODE = 'A'
       AND OD3.PRODUCT_CODE NOT LIKE '%JOIN%'
       AND OD3.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       AND OD3.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       group by OD3.SHIP_MASTER_CUSTOMER_ID
       ) AS MAS 
              ON (OD.SHIP_MASTER_CUSTOMER_ID = MAS.SHIP_MASTER_CUSTOMER_ID)
LEFT JOIN (
       SELECT OMA.MASTER_CUSTOMER_ID
       FROM ORDER_DETAIL OD1
       INNER JOIN ORDER_MBR_ASSOCIATE OMA 
              ON (OD1.ORDER_NO = OMA.ORDER_NO AND OD1.ORDER_LINE_NO = OMA.ORDER_LINE_NO)
       INNER JOIN PRODUCT P1 
              ON (OD1.PRODUCT_ID = P1.PRODUCT_ID)
       WHERE OD1.SUBSYSTEM = 'MBR'
       AND P1.PRODUCT_CLASS_CODE = 'DUES'
       AND OD1.LINE_STATUS_CODE = 'A'
       AND OD1.PRODUCT_CODE NOT LIKE '%JOIN%'
       AND OD1.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       AND OD1.CYCLE_BEGIN_DATE <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
       group by OMA.MASTER_CUSTOMER_ID
       ) AS ASOC 
              ON (OD.SHIP_MASTER_CUSTOMER_ID = ASOC.SHIP_MASTER_CUSTOMER_ID)

WHERE (OD.PRODUCT_CODE LIKE '%CC%' OR OD.PRODUCT_CODE LIKE '%PS%' OR OD.PRODUCT_CODE LIKE '%LC%')
AND OD.CYCLE_END_DATE >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.CYCLE_BEGIN_DATE <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
AND OD.LINE_STATUS_CODE <> 'C'
AND OD.PRODUCT_CODE NOT LIKE '%_FEE'
AND OD.SUBSYSTEM = 'MTG'