IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)
AND VIP_FLG_SBB in ('E','H','0','1','9','B','C','F','G','L','M','N','O','P','Q','R','S',
				    'U','V','W','X','Y','Z')


Select DISTINCT
O.Prin_oci,
O.SYS_OCI,
O.AGNT_OCI,
O.SUB_ACCT_NO_OCI,
A.RES_NAME_SBB,
CASE WHEN A.EXT_STAT_SBB = '' THEN 'ACTIVE'
WHEN A.EXT_STAT_SBB = 'C' THEN 'VOL_DISCO'
WHEN A.EXT_STAT_SBB = 'E' THEN 'NON-PAY_DISCO'
WHEN A.EXT_STAT_SBB = 'Z' THEN 'CHARGED_OFF'
ELSE '' END AS 'ACCOUNT_STATUS',
CASE WHEN A.VIP_FLG_SBB = '0' THEN 'VIP_TENANT'
	 WHEN A.VIP_FLG_SBB = '1' THEN 'GOVT_RESI'
	 WHEN A.VIP_FLG_SBB = '9' THEN 'GOVT_COMM'
	 WHEN A.VIP_FLG_SBB = 'B' THEN 'BULK_MASTER'
	 WHEN A.VIP_FLG_SBB = 'C' THEN 'COMM_GOVT'
	 WHEN A.VIP_FLG_SBB = 'E' THEN 'COMCAST_EMPLOYEE'
	 WHEN A.VIP_FLG_SBB = 'F' THEN 'COMP_SERVICE'
	 WHEN A.VIP_FLG_SBB = 'G' THEN 'COMM_GOVT'
	 WHEN A.VIP_FLG_SBB = 'H' THEN 'COMCAST_EMPTENT'
	 WHEN A.VIP_FLG_SBB = 'L' THEN 'LIBRARY_RES'
	 WHEN A.VIP_FLG_SBB = 'M' THEN 'APT_MANAGER'
	 WHEN A.VIP_FLG_SBB = 'N' THEN 'NON_BB_TENANT'
	 WHEN A.VIP_FLG_SBB = 'O' THEN 'BRGND_EMPLOYEE'
	 WHEN A.VIP_FLG_SBB = 'P' THEN 'TENANT_MANAGER'
	 WHEN A.VIP_FLG_SBB = 'Q' THEN 'RECIPROCAL_TENANT'
	 WHEN A.VIP_FLG_SBB = 'R' THEN 'RECIPROCAL'
	 WHEN A.VIP_FLG_SBB = 'S' THEN 'SCHOOL_RES'
	 WHEN A.VIP_FLG_SBB = 'U' THEN 'SEASONAL'
	 WHEN A.VIP_FLG_SBB = 'V' THEN 'VIP'
	 WHEN A.VIP_FLG_SBB = 'W' THEN 'COM_DQ_EXEMPT'
	 WHEN A.VIP_FLG_SBB = 'X' THEN 'SPECT/STRT/NBC'
	 WHEN A.VIP_FLG_SBB = 'Y' THEN 'RETIRED_EMPLOYEE'
	 WHEN A.VIP_FLG_SBB = 'Z' THEN 'SPEC/ST/NBC-TEN'
ELSE '' END AS 'VIP_FLG_SBB',
H.Addr1_HSE as ADDR1,
H.Res_Addr_2_HSE as APT,
H.Res_city_HSE as CITY,
H.Postal_cde_HSE as ZIP_CODE,
CASE WHEN O.LOB_ACT_OCI = 'C' THEN 'VIDEO'
	 WHEN O.LOB_ACT_OCI = 'I' THEN 'HSD'
	 WHEN O.LOB_ACT_OCI = 'T' THEN 'VOICE'
ELSE '' END AS 'LOB',

MAX(O.CYC_CHG_DTE_OCI) AS CYC_CHG_DTE_OCI,
SUM(O.CHARGE_AMT_OCI) AS CHARGE_AMT_OCI,
O.SERV_CDE_OCI,
O.DSC_CDE_OCI,
cd.ONLINE_DESC_ALA,
A.CUR_BAL_SBB,
ctd.SPA_FLG_CTD

INTO #TMP2C

FROM #TMP1C as A (nolock) inner join Vantage.dbo.OCI_CUR_ITEM AS O (NOLOCK)
ON A.PRIN_SBB = O.PRIN_OCI
AND A.SUB_ACCT_NO_SBB = O.SUB_ACCT_NO_OCI

inner join Vantage.dbo.HSE_Base as H (nolock) on O.HSE_KEY_OCI = H.HSE_KEY_HSE

inner join Vantage.dbo.CTD_DISPLAY as CTD (nolock) 
on A.PRIN_SBB = ctd.PRIN_CTD
and A.VIP_FLG_SBB = ctd.CDE_VALUE_CTD

inner join Vantage.dbo.ALA_SERV_CODE as CD (nolock)
on o.PRIN_OCI = cd.PRIN_ALA
and o.SERV_CDE_OCI = cd.SERV_CDE_ALA

inner join Vantage.dbo.PAJ_ADJUST as AJ (nolock)
on cd.PRIN_ALA = aj.PRIN_PAJ
and cd.SERV_CDE_ALA = aj.SERV_CDE_PAJ


WHERE CTD.SPA_FLG_CTD = 'P'
AND CTD.CDE_TBL_NO_CTD = '24'
AND O.LOB_ACT_OCI NOT IN ('','N','Y','Z')
AND O.CHARGE_AMT_OCI <> 0
AND O.SUB_ACCT_NO_OCI = '8495600010006437'

group BY
O.Prin_oci,
O.SYS_OCI,
O.AGNT_OCI,
O.SUB_ACCT_NO_OCI,
A.RES_NAME_SBB,
A.EXT_STAT_SBB ,
A.VIP_FLG_SBB,
H.Addr1_HSE,
H.Res_Addr_2_HSE,
H.Res_city_HSE,
H.Postal_cde_HSE,
O.LOB_ACT_OCI,
O.CYC_CHG_DTE_OCI,
O.CHARGE_AMT_OCI,
O.SERV_CDE_OCI,
O.DSC_CDE_OCI,
cd.ONLINE_DESC_ALA,
A.CUR_BAL_SBB,
ctd.SPA_FLG_CTD

SELECT distinct AB.Prin_oci,
AB.SYS_OCI,
AB.AGNT_OCI,
AB.SUB_ACCT_NO_OCI,
AB.RES_NAME_SBB,
AB.ACCOUNT_STATUS ,
AB.VIP_FLG_SBB,
AB.ADDR1,
AB.APT,
AB.CITY,
AB.ZIP_CODE,
AB.LOB,
AB.CYC_CHG_DTE_OCI,
AB.CHARGE_AMT_OCI,
AB.SERV_CDE_OCI,
AB.ONLINE_DESC_ALA,
AB.DSC_CDE_OCI,
DC.DSC_CDE_DSC, 
SUM(DC.DSC_VAL_DSC) AS DSC_VAL_DSC, 
DC.LOB_DSC, 
DC.STMT_DESCR_DSC,
AB.CUR_BAL_SBB

FROM #TMP2C AS AB INNER JOIN Vantage.dbo.DSC_DISCOUNT AS DC (NOLOCK)
ON AB.PRIN_OCI = DC.PRIN_DSC
AND AB.SYS_OCI = DC.SYS_DSC
AND AB.AGNT_OCI = DC.AGNT_DSC
AND AB.DSC_CDE_OCI = DC.DSC_CDE_DSC

where dc.CDE_TBL_NO_DSC = '44'
and ab.spa_flg_ctd = 'P'

				  
group BY
AB.Prin_oci,
AB.SYS_OCI,
AB.AGNT_OCI,
AB.SUB_ACCT_NO_OCI,
AB.RES_NAME_SBB,
AB.ACCOUNT_STATUS ,
AB.VIP_FLG_SBB,
AB.ADDR1,
AB.APT,
AB.CITY,
AB.ZIP_CODE,
AB.LOB,
AB.CYC_CHG_DTE_OCI,
AB.CHARGE_AMT_OCI,
AB.SERV_CDE_OCI,
AB.ONLINE_DESC_ALA,
AB.DSC_CDE_OCI,
DC.DSC_CDE_DSC, 
DC.DSC_VAL_DSC, 
DC.LOB_DSC, 
DC.STMT_DESCR_DSC,
AB.CUR_BAL_SBB