/*
 * This is an Oracle query used on a database that was extracted from
 * the database for the ATandT Sablime source code management product.
 * This query extracts the names of persons who no longer work for the
 * company but who are Sablime users and have entries in the database.
 * 20OCT1998 Jon Kettenhofen
 *
 */
 
SELECT M.Product, MG.generic, M.originator_id, M.mra_study_developer,
		M.creator, MG.developer, M.mr_number, MG.mrg_status, M.mr_status
	FROM
		casdb.pts_master P, casdb.mr_generic MG, casdb.mr_master M
	WHERE
			M.mr_number = MB.mr_number (+)
		AND
		(
				MG.mrg_status LIKE '%assigned%'
			OR
				MG.mrg_status LIKE '%understudy%'
			OR
				MG.mrg_status LIKE '%mra_study%'
		)
		AND
		(
				P.pts_id = M.mra_study_developer
			OR
				P.pts_id = M.originator_id
			OR
				P.pts_id = MG.creator
			OR
				P.pts_id = MB.developer
		)
		AND
		(
				P.dept_id like '%_erminated%'
			OR
				P.full_name like '%_erminated%'
		)
	ORDER BY product, creator, originator_id, developer, mr_number ASC;