Page Comparison

WITH ChildOrgList As (
	SELECT
		ChildOrg.[CHART_CD], ChildOrg.[ORG_CD], ChildOrg.[ORG_PARENT_ORG_CD]
	FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg
	WHERE ChildOrg.CHART_CD = 'IR' and ChildOrg.ORG_CD = '9014'
	UNION ALL
	SELECT
		ChildOrg.[CHART_CD], ChildOrg.[ORG_CD], ChildOrg.[ORG_PARENT_ORG_CD]
	FROM [dwhs_ods].[dbo].[kfs_d_org] as ChildOrg
		INNER JOIN ChildOrgList as Child ON ChildOrg.[ORG_PARENT_ORG_CD] = Child.ORG_CD
			AND ChildOrg.[ORG_PARENT_CHART_CD] = Child.CHART_CD
)
SELECT 
	a.FIN_COA_CD, a.ACCOUNT_NBR, a.ACCOUNT_NM, a.ACCT_FSC_OFC_UID, a.ACCT_SPVSR_UNVL_ID, a.ACCT_MGR_UNVL_ID,
	a.ORG_CD, a.ACCT_TYP_CD, a.ACCT_PHYS_CMP_CD, a.SUB_FUND_GRP_CD, a.ACCT_FRNG_BNFT_CD, a.FIN_HGH_ED_FUNC_CD,
	a.ACCT_RSTRC_STAT_CD, a.ACCT_RSTRC_STAT_DT, a.ACCT_CITY_NM, a.ACCT_STATE_CD, a.ACCT_STREET_ADDR,
	a.ACCT_ZIP_CD, a.RPTS_TO_FIN_COA_CD, a.RPTS_TO_ACCT_NBR, a.ACCT_CREATE_DT, a.ACCT_EFFECT_DT,
	a.ACCT_EXPIRATION_DT, a.CONT_FIN_COA_CD, a.CONT_ACCOUNT_NBR, a.ENDOW_FIN_COA_CD, a.ENDOW_ACCOUNT_NBR,
	a.CONTR_CTRL_FCOA_CD, a.CONTR_CTRLACCT_NBR, a.INCOME_FIN_COA_CD, a.INCOME_ACCOUNT_NBR, a.ACCT_ICR_TYP_CD,
	a.AC_CSTM_ICREXCL_CD, a.FIN_SERIES_ID, a.ICR_FIN_COA_CD, a.ICR_ACCOUNT_NBR, a.ACCT_IN_FP_CD, a.BDGT_REC_LVL_CD,
	a.ACCT_SF_CD, a.ACCT_PND_SF_CD, a.FIN_EXT_ENC_SF_CD, a.FIN_INT_ENC_SF_CD, a.FIN_PRE_ENC_SF_CD,
	a.FIN_OBJ_PRSCTRL_CD, a.CG_CFDA_NBR, a.ACCT_OFF_CMP_IND, a.ACCT_CLOSED_IND, a.CG_ACCT_RESP_ID,
	e.UC_LOC_CD, e.UC_ACCT_NBR, e.UC_FUND_NBR, e.UCI_CAPITAL_CAT_CD
FROM CA_ACCOUNT_T a
	LEFT OUTER JOIN CA_ACCOUNT_EXT_T e on (a.FIN_COA_CD = e.FIN_COA_CD and a.ACCOUNT_NBR = e.ACCOUNT_NBR)
WHERE
	a.ACCT_CLOSED_IND <> 'Y'
	AND (a.ACCT_EXPIRATION_DT >= getdate() or a.ACCT_EXPIRATION_DT IS NULL)
	AND a.ACCT_EFFECT_DT <= GETDATE()
	AND EXISTS 
		(select 1 from ChildOrgList o WHERE o.CHART_CD = a.FIN_COA_CD and o.org_cd = a.ORG_CD)