Query blues... Help!

I have a security app that is like this: a Facility has many Apps and those
Apps may have any combination Modules, Roles, or Privileges.

Problem Query: Put together a query(s) that will delete all records related
to a single Facility.

CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
go
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
go
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table
between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED
(AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
go
CREATE TABLE ModulesRolesPrivilegesHierarchy
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES
ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))

INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host
Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft')
-- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv')
-- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv')
-- 4

INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2)
-- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2)
-- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1)
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3)
-- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3)
-- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 9

INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4

INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (8, 9) -- 8

.