Working with multiple level hierarchies in a table

First of all, when you ask a question, please provide your example data as a table. The example data you provided was a complete mess. It took longer to sort it out than it did to write your query.

Also, please provide as much DDL and DML as is relevant. We have no idea what data types these columns should be.

DECLARE @lazyMess TABLE (CUSTOMER_NAME VARCHAR(4), ALTERNATE_ID INT, AGR_TYPE VARCHAR(20), OPPORTUNITY_ID INT, AGR_NBR VARCHAR(20), MASTER_AGR VARCHAR(20), AGR_NAME VARCHAR(20), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE)
INSERT INTO @lazyMess (CUSTOMER_NAME, ALTERNATE_ID, AGR_TYPE, OPPORTUNITY_ID, AGR_NBR, MASTER_AGR, AGR_NAME, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
VALUES
('XYZ', 10502, 'Master Agreement', 10501,'MAY-284491', NULL, 'May Test', '20141007' ,'20151007'), 
('XYZ', 13587, 'Master Agreement', 13586,'MAY-284492', NULL, 'May 284492', '20141007' ,'20151007'), 
('ABCD', 25008, 'Satellite Agreement', 25007,'WHATEVS', 'MITAC', 'Next', '20141007' ,'20151007'), 
('ABCD', 0, 'Satellite Agreement', 1 ,'ABC','WHATEVS', NULL, '20141007' ,'20151007'), 
('ABDC', 13525, 'Master Agreement', 13524,'MITAC', NULL, 'Test', '20141007' ,'20151007'), 
('ABCD', 1, 'Satellite Agreement', 2 ,'DEF','MITAC', NULL, '20141007' ,'20151007') 

;WITH rCTE AS (
SELECT *, CAST(agr_nbr AS VARCHAR(20)) AS parent_agr
  FROM @lazyMess
 WHERE master_agr IS NOT null
UNION ALL
SELECT lm.CUSTOMER_NAME, lm.ALTERNATE_ID, lm.AGR_TYPE, lm.OPPORTUNITY_ID, lm.AGR_NBR, lm.MASTER_AGR, lm.AGR_NAME, lm.EFFECTIVE_START_DATE, lm.EFFECTIVE_END_DATE, r.agr_nbr
  FROM rCTE r
    INNER JOIN @lazyMess lm
      ON r.MASTER_AGR = lm.AGR_nbr
)

SELECT *
  FROM rCTE
 WHERE parent_agr = 'Whatevs'