Cumulative Related Records From Hierarchy

Morning chucky eggs

This is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798

SQL 2k5

I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.

Below is
1) Set up code (in red)
2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly
3) My new soulution.

My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.

USE tempdb
go

---------------------------------------------------------------
-- Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
(
unit_code VARCHAR(10) NOT NULL
, parent_code VARCHAR(10) NULL
, CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
)
GO

CREATE TABLE dbo.org_peeps
(
unit_code VARCHAR(10) NOT NULL
, person_code INT NOT NULL
, CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
)
GO

CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
SELECT org.unit_code
, org.parent_code
, org_peeps.person_code
FROM dbo.org
LEFT OUTER JOIN
dbo.org_peeps
ON org_peeps.unit_code = org.unit_code
GO

INSERT INTO dbo.org (unit_code, parent_code)
SELECT 'a', 'y' UNION ALL
SELECT 'b', 'y' UNION ALL
SELECT 'c', 'z' UNION ALL
SELECT 'y', 'z' UNION ALL
SELECT 'z', NULL

--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'a', 1 UNION ALL
SELECT 'c', 3 UNION ALL
--These data go into intermediate levels of the organisation...
SELECT 'y', 4 UNION ALL
SELECT 'y', 5 UNION ALL
SELECT 'y', 6

---------------------------------------------------------------
SELECT 'Original query. Because there is data in the intermediate tables --> ' +
'duplicate outputs....'
---------------------------------------------------------------
;WITH materialised_paths
AS
(
SELECT unit_code
, parent_code
, person_code
, unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROM dbo.org_peeps_parents
WHERE parent_code IS NULL
UNION ALL
SELECT all_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROM dbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ON mp.unit_code = all_people.parent_code
)
SELECT parents.unit_code
, children.person_code
FROM materialised_paths AS children
CROSS APPLY --Correlated derived table - get the child records per unit
(
SELECT unit_code
, parent_code
FROM dbo.org AS parents_sub
WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHERE children.person_code IS NOT NULL
ORDER BY person_code
, unit_code

-----------------------------------------------------------------
SELECT 'This query returns the correct results but man it is ugly. '+
'Can it be refined optimised?'
-----------------------------------------------------------------
;WITH materialised_paths
AS
(
SELECT unit_code
, parent_code
, unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROM dbo.org
WHERE parent_code IS NULL
UNION ALL
SELECT all_orgs.unit_code
, all_orgs.parent_code
, mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/'
FROM dbo.org AS all_orgs
INNER JOIN
materialised_paths AS mp
ON mp.unit_code = all_orgs.parent_code
)
SELECT all_orgs.unit_code
, org_peeps.person_code
FROM dbo.org_peeps
INNER JOIN
(
SELECT org.unit_code
, mp.unit_path
FROM materialised_paths AS mp
CROSS APPLY
(
SELECT unit_code
, parent_code
FROM dbo.org
WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%'
) AS org
) AS all_orgs
ON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'
ORDER BY person_code
, all_orgs.unit_code

--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
DROP VIEW dbo.org_peeps_parents
END

IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
DROP TABLE dbo.org
ENDAs ever kudos and lavish thanks to anyone that can help :D

Oh - I nearly forgot - accounting for the Rudy clause - desired output:

unit_code person_code
---------- -----------
a 1
y 1
z 1
c 3
z 3
y 4
z 4
y 5
z 5
y 6
z 6