SELECT pty_id, cross_src_id, per_src_sys_c, survivor_i, func_mgr_nm, stfg_rep_nm, hr_rep_nm, alt_cntct_nm     
INTO #t_cs_pty_ext
FROM pc2cnfdbo.t_cs_pty_ext
WHERE pty_ext_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_ext
ON #t_cs_pty_ext(pty_id);

SELECT pty_id, posn_asgt_id
INTO #t_cs_job_posn
FROM pc2cnfdbo.t_cs_job_posn
WHERE job_posn_end_d IS NULL;
 
CREATE CLUSTERED INDEX temp_t_cs_job_posn
ON #t_cs_job_posn(pty_id);

SELECT pty_id, posn_asgt_id
	posn_rltnshp_id, posn_rltnshp_efctv_d, posn_rltnshp_end_d, per_role_c
	,related_pty_id, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_posn_rltnshp
FROM pc2cnfdbo.t_cs_posn_rltnshp
WHERE posn_rltnshp_end_d IS NULL
	AND posn_rltnshp_sts_c = 'A';

CREATE CLUSTERED INDEX temp_t_cs_posn_rltnshp
ON #t_cs_posn_rltnshp(pty_id, posn_asgt_id);

SELECT pty_id, id_ty_c,
	id_val, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_idntfctn
FROM pc2cnfdbo.t_cs_pty_idntfctn
WHERE pty_idntfctn_end_d IS NULL
	AND id_ty_c IN ('03', '10')
	AND cur_per_id_i = 'Y';

CREATE CLUSTERED INDEX temp_t_cs_pty_idntfctn
ON #t_cs_pty_idntfctn(pty_id, id_ty_c);

SELECT pty_id
	,full_legal_nm, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_nm
FROM pc2cnfdbo.t_cs_pty_nm
WHERE pty_nm_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_nm
ON #t_cs_pty_nm(pty_id);

SELECT pty_id
       ,mz_ref_s_id, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_pty_mz
FROM pc2cnfdbo.t_cs_pty_mz
WHERE pty_mz_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_pty_mz
ON #t_cs_pty_mz(pty_id);

SELECT mz_ref_s_id
	,mz_c, cre_d, cre_by_bdge_n_c, upd_d, upd_bdge_n_c
INTO #t_cs_mz_ref_s
FROM pc2cnfdbo.t_cs_mz_ref_s
WHERE mz_ref_s_end_d IS NULL;

CREATE CLUSTERED INDEX temp_t_cs_mz_ref_s
ON #t_cs_mz_ref_s(mz_ref_s_id);

SELECT A.pty_id, 
       A.cross_src_id, 
       A.per_src_sys_c, 
       A.survivor_i, 
       D.posn_asgt_id, 
       D.posn_rltnshp_id, 
       D.posn_rltnshp_efctv_d, 
       D.posn_rltnshp_end_d, 
       D.per_role_c, 
       D.related_pty_id, 
       E.id_ty_c AS TY_SSN, 
       E.id_val AS RELATED_PTY_SSN, 
       G.full_legal_nm, 
       F.id_ty_c TY_CORPID, 
       F.id_val AS RELATED_TY_CORPID, 
       H.mz_ref_s_id, 
       I.mz_c, 
       D.cre_d, 
       D.cre_by_bdge_n_c, 
       D.upd_d, 
       D.upd_bdge_n_c, 
       E.cre_d, 
       E.cre_by_bdge_n_c, 
       E.upd_d, 
       E.upd_bdge_n_c, 
       F.cre_d, 
       F.cre_by_bdge_n_c, 
       F.upd_d, 
       F.upd_bdge_n_c, 
       G.cre_d, 
       G.cre_by_bdge_n_c, 
       G.upd_d, 
       G.upd_bdge_n_c, 
       H.cre_d, 
       H.cre_by_bdge_n_c, 
       H.upd_d, 
       H.upd_bdge_n_c, 
       I.cre_d, 
       I.cre_by_bdge_n_c, 
       I.upd_d, 
       I.upd_bdge_n_c, 
       B.cross_src_id AS RELATED_PERSON_ID, 
       A.func_mgr_nm, 
       A.stfg_rep_nm, 
       A.hr_rep_nm, 
       A.alt_cntct_nm 
FROM #t_cs_pty_ext A
	JOIN #t_cs_job_posn C
		ON A.pty_id = C.pty_id
	JOIN #t_cs_posn_rltnshp D
		ON A.pty_id = D.pty_id
			AND C.posn_asgt_id = D.posn_asgt_id
	JOIN #t_cs_pty_ext B
		ON B.pty_id = D.related_pty_id
	JOIN #t_cs_pty_idntfctn E
		ON D.related_pty_id = E.pty_id
		   AND E.id_ty_c = '03' 
	JOIN #t_cs_pty_idntfctn F
		ON D.related_pty_id = F.pty_id
		   AND F.id_ty_c = '10' 
	JOIN #t_cs_pty_nm G
		ON D.related_pty_id = G.pty_id
	JOIN #t_cs_pty_mz H
		ON D.related_pty_id = H.pty_id
	JOIN #t_cs_mz_ref_s I
		ON H.mz_ref_s_id = I.mz_ref_s_id