IF OBJECT_ID('TEMPDB..#TMP1CE') IS NOT NULL DROP TABLE #TMP1CE
IF OBJECT_ID('TEMPDB..#TMPCSGACCT1') IS NOT NULL DROP TABLE #TMPCSGACCT1
IF OBJECT_ID('TEMPDB..#TMPCSGACCT2') IS NOT NULL DROP TABLE #TMPCSGACCT2
IF OBJECT_ID('TEMPDB..#TMPCSGACCT3') IS NOT NULL DROP TABLE #TMPCSGACCT3
IF OBJECT_ID('TEMPDB..#TMPCSGACCT4') IS NOT NULL DROP TABLE #TMPCSGACCT4
IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
IF OBJECT_ID('TEMPDB..#TMP3C') IS NOT NULL DROP TABLE #TMP3C



SELECT DISTINCT [PRIN_EQL], [EQP_SERIAL_EQL], [EQP_LOCAL_EQL], [EQP_STAT_EQL], 
[START_DTE_EQL]

INTO #TMP1CE

FROM [Vantage].[dbo].[EQL_HIST] (NOLOCK)

WHERE EQP_STAT_EQL IN ('R','L')
AND EQP_LOCAL_EQL LIKE ('8495%')
AND [START_DTE_EQL] BETWEEN '2013-10-01' AND '2014-09-30'

SELECT      DISTINCT A.*,EQ.[EQP_LOCAL_EQP],EQ.[EQP_SERIAL_EQP],EQN.[EQP_STAT_EQP] AS EquipStatNow
FROM  #TMP1CE A WITH(NOLOCK) LEFT JOIN [Vantage].[dbo].[EQP_BASE20140921] EQ WITH(NOLOCK)
ON          A.PRIN_EQL=EQ.PRIN_EQP 
AND         A.[EQP_SERIAL_EQL]=EQ.[EQP_SERIAL_EQP]
AND			A.[EQP_STAT_EQL] = EQ.[EQP_STAT_EQP]

            LEFT JOIN [Vantage].[dbo].[EQP_BASE] EQN WITH(NOLOCK)
ON          EQ.PRIN_EQP=EQN.PRIN_EQP 
AND         EQ.[EQP_SERIAL_EQP]=EQN.[EQP_SERIAL_EQP] 
AND			EQN.PRIN_EQP IN ('6000','7500')
	



CREATE TABLE #TMPCSGACCT1 (
      ITEM# NUMERIC (7,0),
      SUB_ACCT_NO VARCHAR (MAX)
)
INSERT INTO #TMPCSGACCT1 (ITEM#,SUB_ACCT_NO)

SELECT      ITEM#,
            rtrim(ltrim([SUB_ACCT_NO])) AS SUB_ACCT_NO

FROM [AuditGroup].dbo.EquipRetDataTest (NOLOCK)

CREATE TABLE #TMPCSGACCT2 (
      ITEM# NUMERIC (7,0),
      SUB_ACCT_NO VARCHAR (MAX)
)

INSERT INTO #TMPCSGACCT2 (ITEM#,SUB_ACCT_NO)

SELECT      ITEM#,SUB_ACCT_NO
FROM  #TMPCSGACCT1 WITH(NOLOCK)
WHERE LEFT([SUB_ACCT_NO],6) IN ('849560','849575')

CREATE TABLE #TMPCSGACCT3 (
      ITEM# NUMERIC (7,0),
      PRIN SMALLINT,
      SUB_ACCT_NO CHAR (16)
)
INSERT INTO #TMPCSGACCT3 (ITEM#,PRIN,SUB_ACCT_NO)

SELECT      ITEM#,
            CASE WHEN LEFT([SUB_ACCT_NO],6) = '849560' THEN '6000'
            --WHEN LEFT([SUB_ACCT_NO],6) = '849574' THEN '7400'
            WHEN LEFT([SUB_ACCT_NO],6) = '849575' THEN '7500'
            ELSE '' END AS PRIN,
            SUB_ACCT_NO

FROM  #TMPCSGACCT2 WITH(NOLOCK)

CREATE TABLE #TMPCSGACCT4 (
      ITEM# NUMERIC (7,0),
      PRIN SMALLINT,
      SUB_ACCT_NO CHAR (16),
      HSE_KEY_SBB CHAR (14),
      [CUST_ACCT_NO_SBB] CHAR(13),
      [RES_NAME_SBB] VARCHAR (26),
      [CUR_BAL_SBB] DECIMAL(9,2)
)

INSERT INTO #TMPCSGACCT4 (ITEM#,PRIN,SUB_ACCT_NO,[HSE_KEY_SBB],[CUST_ACCT_NO_SBB],[RES_NAME_SBB],[CUR_BAL_SBB])

SELECT      A.ITEM#,A.PRIN,A.SUB_ACCT_NO,S.[HSE_KEY_SBB],S.[CUST_ACCT_NO_SBB],S.[RES_NAME_SBB], S.[CUR_BAL_SBB]

FROM        #TMPCSGACCT3 A WITH(NOLOCK) INNER JOIN [Vantage].[dbo].[SBB_BASE] S WITH(NOLOCK)
ON          A.PRIN=S.PRIN_SBB AND
           A.SUB_ACCT_NO=S.SUB_ACCT_NO_SBB AND S.PRIN_SBB IN ('6000','7500')


select 
T4.PRIN,
T4.SUB_ACCT_NO,
T4.HSE_KEY_SBB,
T4.RES_NAME_SBB,
T4.CUR_BAL_SBB,
h.acct_stage_ohi,
h.connect_dte_ohi, 
h.disco_dte_ohi, 
h.item_status_ohi


INTO #TMP1C

from #TMPCSGACCT4 as T4 (nolock) inner join vantage.dbo.ohi_hist_item as h (nolock)

ON          T4.PRIN = H.PRIN_OHI
and         T4.SUB_ACCT_NO = h.SUB_ACCT_NO_OHI and h.prin_ohi in ('6000','7500')

where h.DISCO_DTE_OHI > h.CONNECT_DTE_OHI AND
            H.ITEM_STATUS_OHI IN ('C','O','P','B') 
            and h.acct_stage_ohi in ('V','N')
            and t4.item# = 1


SELECT      A.PRIN,
            A.SUB_ACCT_NO,
            A.ACCT_STAGE_OHI,
            A.RES_NAME_SBB,
            HS.ADDR1_HSE,
            HS.RES_CITY_HSE,
            HS.RES_STATE_HSE,
            HS.POSTAL_CDE_HSE,            
            A.CUR_BAL_SBB,
            A.CONNECT_DTE_OHI,
            max(A.disco_dte_ohi) as disco_dte_ohi 

INTO  #TMP2C


FROM  #TMP1C A WITH(NOLOCK) INNER JOIN Vantage.dbo.HSE_BASE HS WITH (NOLOCK)

ON		A.HSE_KEY_SBB = HS.HSE_KEY_HSE
--AND		A.SUB_ACCT_NO = HS.SUB_ACCT_NO_HSE

GROUP BY		  A.PRIN,
                  A.SUB_ACCT_NO,
                  A.ACCT_STAGE_OHI,
                  A.RES_NAME_SBB,
                  HS.ADDR1_HSE,
				  HS.RES_CITY_HSE,
				  HS.RES_STATE_HSE,
				  HS.POSTAL_CDE_HSE,
				  A.CUR_BAL_SBB,
                  A.CONNECT_DTE_OHI

SELECT		B.PRIN,
            B.SUB_ACCT_NO,
            B.ACCT_STAGE_OHI,
            B.RES_NAME_SBB,
            B.ADDR1_HSE,
            B.RES_CITY_HSE,
            B.RES_STATE_HSE,
            B.POSTAL_CDE_HSE,
            M.TRAN_AMT_MON,            
            B.CUR_BAL_SBB,
            B.CONNECT_DTE_OHI,
            B.disco_dte_ohi,
            M.TRAN_DTE_MON 

INTO #TMP3C            
            
FROM #TMP2C B WITH (NOLOCK) INNER JOIN Vantage.dbo.MON_TRAN_BASE M WITH (NOLOCK)

ON		B.PRIN = M.PRIN_MON
AND		B.SUB_ACCT_NO = M.SUB_ACCT_NO_MON

WHERE M.TRAN_CDE_MON = '287'

SELECT				C.PRIN,
					C.SUB_ACCT_NO AS ACCOUNT_NUMBER,
					CASE WHEN C.ACCT_STAGE_OHI = 'V' THEN 'VOL_DISCONNECT'
						 WHEN C.ACCT_STAGE_OHI = 'N' THEN 'NON_PAY_DISCONNECT'
						 ELSE '' END AS 'CUSTOMER_STATUS',
					C.RES_NAME_SBB AS CUSTOMER_NAME,
					C.ADDR1_HSE AS [ADDRESS],
					C.RES_CITY_HSE AS CITY,
					C.RES_STATE_HSE AS [STATE],
					C.POSTAL_CDE_HSE AS ZIP_CODE,
					C.TRAN_AMT_MON AS REFUND_AMOUNT,            
					C.CUR_BAL_SBB AS CURRENT_BALANCE,
					C.CONNECT_DTE_OHI AS CONNECT_DATE,
					C.disco_dte_ohi AS DISCONNECT_DATE,
					C.TRAN_DTE_MON AS REFUND_DATE,
					EQ.STAT_CHG_DTE_EQP,
					EQ.CREATE_STAT_EQP,
					EQ.BOX_STAT_EQP,
					EQ.EQP_STAT_EQP,
					--EQ2.EQP_STAT_EQL,
					--EQ2.EQP_SERIAL_EQL
					EQ.EQP_SERIAL_EQP 
					
FROM #TMP3C C WITH (NOLOCK) LEFT OUTER JOIN Vantage.dbo.EQP_BASE EQ WITH (NOLOCK)

ON C.SUB_ACCT_NO = EQ.EQP_LOCAL_EQP