Ok, I've taken out the data manipulation and left the main loop inserts.
----------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[DEV_AL_ECL_TEST_PROC] AS
DECLARE @DATE_BEG DATETIME, @DATE_END DATETIME,
@DATE_MID DATETIME, @DATE_NAME VARCHAR(20),
@time char(50), @START_TIME AS DATETIME, @RUN_MONTH AS DATETIME,
@run_time as datetime,
@startdate datetime , @enddate datetime
SET @RUN_MONTH = (SELECT MAX(CAST(DT_BEG AS DATETIME))
FROM AL_ECL_DATES )
set @run_time = getdate()
SET DEADLOCK_PRIORITY 'HIGH'
BEGIN -- MAIN
CREATE TABLE #AL_ECL_STAGING
(AUTH_ID varchar(20), ANALYSISGRP varchar(20), ASSIGNED_MDC_CD varchar(6),
ASSIGNED_MDC_DESC varchar(100), BED_TYPE_CD varchar(8), BEN_CD varchar(10),
BEN_PLAN_CD varchar(15), CAP_LINE_FLG varchar(2), CAPACITY_CD varchar(10),
CASE_ID varchar(10), CAT_CLM_AMT money DEFAULT 0, CAT_MEM_AMT money DEFAULT 0,
CATGY_CD varchar(10), CI_CD varchar(2), CLAIM_STATUS varchar(2),
CLM_ADJUST_CD varchar(20), CLM_ID varchar(30), CLM_LINE_NO varchar(20),
CLM_NO varchar(20), CLM_TYPE_CD varchar(4), COB_AMT money DEFAULT 0,
COIN_AMT money DEFAULT 0, COMP_ID varchar(4), COPAY_AMT money DEFAULT 0,
DED_AMT money DEFAULT 0, DELETECOL varchar(1), DENIED_CLM varchar(1),
DIAG_1_GROUP1_DESC varchar(50), DIAG_1_GROUP2_DESC varchar(50), DIAG_GROUPER varchar(10),
DIAG1_GROUP1_CD varchar(10), DIAG1_GROUP2_CD varchar(10), DISC_AMT money DEFAULT 0,
DISCHRG_STAT_CD varchar(4), DIVISION_ID varchar(12), DOS_DT datetime,
DOS_DT_AGE int, DOS_THRU_DT datetime, DX_1_CD varchar(6),
DX_1_DESC varchar(40), DX_2_CD varchar(6), DX_2_DESC varchar(40),
DX_3_CD varchar(6), DX_3_DESC varchar(40), DX_4_CD varchar(6),
DX_4_DESC varchar(40), DX_5_CD varchar(6), DX_5_DESC varchar(40),
DX_6_CD varchar(6), DX_6_DESC varchar(40), DX_7_CD varchar(6),
DX_7_DESC varchar(40), DX_8_CD varchar(6), DX_8_DESC varchar(40),
DX_9_CD varchar(6), DX_9_DESC varchar(40), ECL_ID uniqueidentifier,
FACILITY_NM varchar(100), FEE_SCHD_ID varchar(10), GEO_REGION varchar(30) DEFAULT 'UNASSIGNED',
GL2_1 varchar(3) DEFAULT NULL, GL2_1_DESC varchar(50), GL2_2 varchar(3) DEFAULT NULL,
GL2_2_DESC varchar(50), GL2_3 varchar(3) DEFAULT NULL, GL2_3_DESC varchar(50),
GL2_ALL varchar(12), GL2_ALL_DESC varchar(50), GRP_ID varchar(15) DEFAULT 'X',
GRP_NM varchar(100) DEFAULT 'UNASSIGNED', HCFA_POS_CD varchar(10), INC_BILLED_AMT money DEFAULT 0,
INC_PAID_AMT money DEFAULT 0, INC_TOT_PAID_AMT money DEFAULT 0, LOB_DESC varchar(50),
LOB_ID varchar(10) DEFAULT 'X', MEM_COUNTY varchar(30) DEFAULT 'Z OTHER', MEM_GENDER_CD varchar(3),
MEM_ID varchar(20), MEM_SOC_SEC_NO varchar(20), MIDMONTH datetime,
MOD_CD varchar(5), ORIG_BEN_PLAN_CD varchar(15), ORIG_GRP_ID varchar(15),
ORIG_HCFA_POS_CD varchar(10), ORIG_LOB_ID varchar(10), ORIG_PCP_ID varchar(20),
ORIG_PORG_ID varchar(10), ORIG_PROV_ID varchar(20), ORIG_SPEC_CD varchar(10),
PAID_AMT money DEFAULT 0, PAID_DT datetime, PAR_FLG varchar(3),
PCP_COUNTY varchar(30), PCP_ID varchar(20) DEFAULT NULL, PCP_NM VARCHAR(100) DEFAULT 'UNASSIGNED',
PCP_LEAD_HOSPITAL varchar(50) DEFAULT 'UNASSIGNED', PCP_POD varchar(50) DEFAULT 'UNASSIGNED',
POPULATIONDATE datetime, PORG_ID varchar(10) DEFAULT 'X',
PORG_DESC varchar(50), PREPAY_AMT money DEFAULT 0, PRICING_DRG_CD varchar(4),
PRICING_DRG_DESC varchar(100), PROCEDURE_GROUP_1 varchar(50), PROCEDURE_GROUP_2 varchar(50),
PROV_ID varchar(20), PROV_NM varchar(100), REF_PROV_ID varchar(16),
REPORTLINE varchar(20), REQ_AMT money DEFAULT 0, REVERSED_FLG varchar(1),
RISK_STRAT varchar(50), SERVICE_PAID_DT datetime, SHORT_GROUP_ID varchar(6),
SOURCE varchar(15), SPEC_CD varchar(10) DEFAULT 'UNASSIGNED', SPEC_DESC varchar(100),
SUBR_ID varchar(20), SUPERCOMP varchar(10), SVC_CD varchar(20),
SVC_DESC varchar(40), SVC_ICD9_CD varchar(10), SVC_QTY int default 0,
TOT_PAID_AMT money DEFAULT 0, US_BILL_TYPE_CD varchar(10), VEND_ID varchar(25),
VEND_TYPE_CD varchar(15), VISIT_NO varchar(10), WTHD_AMT money DEFAULT 0,
PLAN_NAME VARCHAR(15), CATGY_DESC VARCHAR(80), CLASS_CD VARCHAR(10),
CLASS_DESC VARCHAR(80), REF_PROV_NM VARCHAR(100), MEM_ZIP VARCHAR(15),
[DUAL] [varchar](1) NULL CONSTRAINT build2_1a DEFAULT ('N'),
[DUAL_STATUS] [varchar](1) NULL CONSTRAINT build2_2a DEFAULT ('N'),
[ESRD] [varchar](1) NULL CONSTRAINT build2_3a DEFAULT ('N'),
[USS] [varchar](1) NULL CONSTRAINT build2_4a DEFAULT ('N'),
[INST] [varchar](1) NULL CONSTRAINT build2_5a DEFAULT ('N'),
[PROPOSED_PLAN] [varchar](100) NULL CONSTRAINT build2_11a DEFAULT ('N'),
[HOSPICE] [varchar](1) NULL CONSTRAINT build2_6a DEFAULT ('N'),
[TERMED] [varchar](1) NULL CONSTRAINT build2_7a DEFAULT ('N'),
[EXCRISK] [varchar](1) NULL CONSTRAINT build2_8a DEFAULT ('N'),
[PART_D] [varchar](1) NULL CONSTRAINT build2_9a DEFAULT ('N'),
[ECI] [varchar](1) NULL CONSTRAINT build2_10a DEFAULT ('N'),
gl_no varchar(30))
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT
PRINT '-------------------------------------------- TEMP TABLES SECTION ----------------------------------------------'
PRINT GETDATE()
PRINT 'BUILDING TEMP GL TABLE'
SELECT GL2_1_CODE, GL2_1_DESCR, GL2_2_CODE, GL2_2_DESCR, GL2_3_CODE, GL2_3_DESCR
INTO #AL_R_CUST_WD_CLAIM_LINE_GL
FROM DEV.DBO.R_CUST_WD_CLAIM_LINE_GL
---Find All Rehab Auths for AL LOBS---
select b.Member_Number,b.Members_LOB,a.precert_id,b.Authorization_Number,a.procedure_from_date,
a.procedure_to_date,a.procedure_decision,a.procedure_bed_type,
a.decision_reason,b.Referring_Physician,b.Primary_Code_Provider_Code
INTO #AL_REHAB_AUTHS
from HSSQL2.STAGING.dbo.precert4 a, HSSQL2.STAGING.dbo.AUTHORIZATION1 b
where a.precert_id = b.Authorization_Number
and b.Members_LOB in (select lobcode from lineofbusinessdim where operationalmarket = 'al' and [grouping] = 'm')
and a.procedure_bed_type in ('19')
and procedure_decision = 'a'
select providerid, min(sourcedatakey) sourcedatakey
into #firstprovider
from providerdim
group by providerid
select p.providerid, fullname, firstname, lastname
into #providerdim
from providerdim p
join #firstprovider f
on p.providerid = f.providerid
and p.sourcedatakey = f.sourcedatakey
group by p.providerid, fullname, firstname, lastname
drop table #firstprovider
PRINT 'BUILDING TEMP SPEC TABLE'
SELECT GL2_2_ASSIGNMENT, GL2_3, SPEC_CD, GL2_3_FOR_PCP, DATA_ELEMENT, herbs_desc
INTO #AL_SPECIALTY
FROM DEV.DBO.R_CUST_SPECIALTY
PRINT 'BUILDING TEMP NDC TABLE'
SELECT MI_NDC, MI_CATGY_CODE, MI_CATGY_DESC, MI_CLASS_CODE, MI_CLASS_DESC
INTO #AL_GROUPER
FROM RX_NDC_GROUPER
PRINT 'BUILDING TEMP ENROLLMENT TABLE'
SELECT ELIG_EFF_DT, ELIG_EXP_DT, MEM_GENDER_CD, MEM_COUNTY, PCP_COUNTY, MEM_ID, LOB_ID, COMP_ID, GRP_ID, REL_CD, MEM_SOC_SEC_NO,
MEM_ZIP, SUPERCOMP, LOB_DESC
INTO #AL_ENROLLMENT2
FROM AL_ENROLLMENT
PRINT '#WD_CLAIM_LINE_GL'
PRINT GETDATE()
SELECT GL2_3_CODE, GL2_1_CODE, GL2_2_CODE, GL2_1_DESCR, GL2_2_DESCR, GL2_3_DESCR
INTO #WD_CLAIM_LINE_GL
FROM dev.dbo.R_CUST_WD_CLAIM_LINE_GL
PRINT 'MEM COUNTY AND ZIP SECTION AND MAX ENROLLMENT INFO SECTION'
SELECT MEM_ID, MAX(ELIG_EFF_DT)MAX_ELIG
INTO #MAX_DT
FROM #AL_ENROLLMENT2
GROUP BY MEM_ID
SELECT T1.MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, LOB_DESC
INTO #MAX_AL_ENROLLMENT
FROM #AL_ENROLLMENT2 T1, #MAX_DT T2
WHERE T1.MEM_ID = T2.MEM_ID
AND T1.ELIG_EFF_DT = T2.MAX_ELIG
GROUP BY T1.MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, LOB_DESC
PRINT 'CREATING TEMP CAP_GL_CODES TABLE'
SELECT GL2_1_CODE,GL2_2_CODE,GL2_3_CODE,GL2_1_DESCR,GL2_2_DESCR,GL2_3_DESCR,GL2_ALL,GL2_ALL_DESCR,CAPTYPE
INTO #AL_R_CUST_ENH_CAP_GL_CODES
FROM R_CUST_ENH_CAP_GL_CODES
PRINT 'CREATING TEMP R_PROVIDER FILE'
SELECT DISTINCT PROV_ID MHC_PROV_ID, PRIM_SPEC_CD MHC_SPEC_CD
INTO #AL_PROVIDER
FROM EIS.DBO.R_PROVIDER
PRINT 'CREATING TEMP R_CUST_ICD9_TO_MDC_DISTINCT'
SELECT ICD9_DEC_CD, ICD9_DESC
INTO #AL_R_CUST_ICD9_TO_MDC_DISTINCT
FROM EIS.DBO.R_ICD9_TO_MDC_DISTINCT
CREATE INDEX ICD9_INDEX ON #AL_R_CUST_ICD9_TO_MDC_DISTINCT
(ICD9_DEC_CD)
--PRINT 'BHAM_GROUP'
SELECT LEFT(GRP_ID,20)AL_GRP_ID, LEFT(GRP_NM,100)GRP_NM_MSTR
INTO #BHAM_GROUP
FROM BHAM_GROUP
GROUP BY LEFT(GRP_ID,20), LEFT(GRP_NM,100)
PRINT 'AL_MM'
SELECT LOB_ID, COMP_ID, SUPERCOMP, BEN_PLAN_CD, MEM_ID, MIDMONTH,
PCP_LEAD_HOSPITAL, MEM_COUNTY, PCP_COUNTY, PCP_ID, GRP_ID,
GEO_REGION, RISK_STRAT, LOB_DESC, MEM_SOC_SEC_NO, GRP_NM,
PLAN_NAME, PORG_ID, PCP_POD, MEM_ZIP, PCP_NM,
DUAL ,DUAL_STATUS ,ESRD ,USS ,INST ,PROPOSED_PLAN ,HOSPICE ,
TERMED ,EXCRISK ,PART_D ,ECI
INTO #AL_MM_ECL
FROM AL_MM
PRINT 'PROVIDER NAME TABLES'
SELECT DISTINCT pcp_id PROV_ID, pcp_nm PROV_NM
INTO #MSTR_PROV_NM
FROM #AL_MM_ECL
where pcp_id is not null
PRINT 'PROVIDER NAME TABLES'
SELECT PROV_ID, PROV_LAST_NM + ', ' + PROV_FIRST_NM AS PROV_NM
INTO #MSTR_PROV_NM2
FROM EIS.DBO.R_PROVIDER
PRINT '#AL_R_CUST_DX_HS_CODING'
select *
INTO #AL_R_CUST_DX_HS_CODING
from DEV.DBO.R_CUST_DX_HS_CODING
PRINT '#AL_R_CUST_HCPCS_PROCEDURE_GROUPER'
select *
INTO #AL_R_CUST_HCPCS_PROCEDURE_GROUPER
from DEV.dbo.R_CUST_HCPCS_PROCEDURE_GROUPER
PRINT '#AL_R_CUST_ENH_LOB'
select *
INTO #AL_R_CUST_ENH_LOB
FROM EIS.DBO.R_CUST_ENH_LOB
PRINT '#AL_R_CUST_DIAG_TO_MDC'
SELECT ASSIGNED_MDC_CD, ICD9_DEC_CD
INTO #AL_R_CUST_DIAG_TO_MDC
FROM DEV.DBO.R_CUST_DIAG_TO_MDC
PRINT '#AL_R_CUST_ASSIGNED_MDC_FULL_DESC'
SELECT ASSIGNED_MDC_DESC, ASSIGNED_MDC_CD
INTO #AL_R_CUST_ASSIGNED_MDC_FULL_DESC
FROM DEV.DBO.R_CUST_ASSIGNED_MDC_FULL_DESC
PRINT '#GRP_LOB'
SELECT GRP_ID, LOB_ID, LOB_DESC, COMP_ID, SUPERCOMP
INTO #GRP_LOB
FROM #AL_MM_ECL
GROUP BY GRP_ID, LOB_ID, LOB_DESC, COMP_ID, SUPERCOMP
PRINT '#AL_R_CUST_DRG_WEIGHTS_2009'
SELECT *
INTO #AL_R_CUST_DRG_WEIGHTS_2009
FROM EIS.dbo.R_CUST_DRG_WEIGHTS_2009
PRINT '#AL_R_CUST_GRP_ID_XWALK_AL_POST_06'
select *
INTO #AL_R_CUST_GRP_ID_XWALK_AL_POST_06
FROM EIS.dbo.R_CUST_GRP_ID_XWALK_AL_POST_06
PRINT '#AL_R_POS_ORIG'
select *
INTO #AL_R_POS_ORIG
from eis.dbo.R_POS_ORIG
PRINT 'TEMP PCP_POD'
SELECT UPIN, POD_NM, POD_EFF_DT, POD_EXP_DT
INTO #AL_R_CUST_PCP_POD
FROM bham.dbo.R_CUST_PCP_POD
PRINT 'PCP PER MEMBER NOT CURRECNTLY ENROLLED'
SELECT MEM_ID, ELIG_EFF_DT, PCP_ID
INTO #AL_ENROLLMENT2_TMP
FROM BHAM.DBO.AL_ENROLLMENT
SELECT MEM_ID, MAX(ELIG_EFF_DT)MAX_DT, CONVERT(VARCHAR(30),NULL)PCP_ID
INTO #MEM_MAX_DT_PCP
FROM #AL_ENROLLMENT2_TMP
GROUP BY MEM_ID
UPDATE T1
SET T1.PCP_ID = T2.PCP_ID
FROM #MEM_MAX_DT_PCP T1, #AL_ENROLLMENT2_TMP T2
WHERE T1.MEM_ID = T2.MEM_ID
AND MAX_DT = ELIG_EFF_DT
DROP TABLE #AL_ENROLLMENT2_TMP
PRINT 'TEMP MEM_ID TO SOCIAL'
SELECT MEM_ID, MEM_SOC_SEC_NO
INTO #AL_SOCIAL
FROM AL_ENROLLMENT
WHERE MEM_SOC_SEC_NO IS NOT NULL
GROUP BY MEM_ID, MEM_SOC_SEC_NO
PRINT 'TEMP ENROL LOB TABLE'
SELECT MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, GRP_ID, LOB_DESC, MAX(ELIG_EFF_DT)MAX_ELIG_DT
INTO #ENROLL_LOB
FROM AL_ENROLLMENT
GROUP BY MEM_ID, SUPERCOMP, COMP_ID, LOB_ID, GRP_ID, LOB_DESC
-------------------------------------------------------------------------------------------------------------------
TRUNCATE TABLE DEV_AL_ECL_TEST
CREATE INDEX ECL1_INDEX ON #AL_ECL_STAGING
(SOURCE)
CREATE INDEX ECL2_INDEX ON #AL_ECL_STAGING
(ORIG_GRP_ID)
CREATE INDEX ECL3_INDEX ON #AL_ECL_STAGING
(ORIG_LOB_ID)
CREATE INDEX ECL4_INDEX ON #AL_ECL_STAGING
(SVC_CD)
CREATE INDEX ECL5_INDEX ON #AL_ECL_STAGING
(MEM_ID)
CREATE INDEX ECL6_INDEX ON #AL_ECL_STAGING
(ORIG_SPEC_CD)
CREATE INDEX ECL7_INDEX ON #AL_ECL_STAGING
(ORIG_HCFA_POS_CD)
CREATE INDEX ECL8_INDEX ON #AL_ECL_STAGING
(CLM_NO)
CREATE INDEX ECL9_INDEX ON #AL_ECL_STAGING
(GRP_ID)
-- BEGIN BUILDING LOOP
while @startdate < = @enddate
BEGIN
BEGIN TRANSACTION
SELECT @DATE_BEG = DT_BEG, @DATE_END = DT_END, @DATE_MID = DT_MID, @DATE_NAME = DT_NAME
FROM AL_ECL_DATES
WHERE DT_BEG = @STARTDATE
PRINT '--------------------------- NEW MONTH ------------------------------'
PRINT @DATE_NAME
PRINT GETDATE()
SET @time= CAST( @DATE_MID AS VARCHAR(10))+ ' AT ' + convert (varchar(30), getdate(), 8)
RAISERROR ('STARTING MONTH ', 0, 1, @time) WITH NOWAIT
SET @START_TIME = GETDATE()
PRINT 'CLAIMS INSERT INTO DEV_AL_ECL_TEST'
INSERT INTO #AL_ECL_STAGING
(AUTH_ID, ECL_ID, CLM_ID, VEND_TYPE_CD, CLM_ADJUST_CD, SUBR_ID, ORIG_PCP_ID, ORIG_BEN_PLAN_CD, ORIG_HCFA_POS_CD, CAPACITY_CD, ORIG_PORG_ID,
ORIG_GRP_ID, ORIG_PROV_ID, MOD_CD, CLM_NO, CLM_LINE_NO, CASE_ID, VISIT_NO, VEND_ID, MEM_ID, ORIG_SPEC_CD, BEN_CD, BED_TYPE_CD,
PAR_FLG, DX_1_CD, DX_2_CD, DX_3_CD, DX_4_CD, DX_5_CD, DOS_DT, DOS_THRU_DT, SVC_CD, CAP_LINE_FLG, SVC_QTY, FEE_SCHD_ID, REQ_AMT,
COPAY_AMT, COIN_AMT, DED_AMT, WTHD_AMT, COB_AMT, PAID_AMT, PAID_DT, PREPAY_AMT, SOURCE, POPULATIONDATE, SHORT_GROUP_ID,
DIVISION_ID, MIDMONTH, DIAG1_GROUP1_CD, DIAG_1_GROUP1_DESC, DIAG1_GROUP2_CD, DIAG_1_GROUP2_DESC, CATGY_CD, DOS_DT_AGE, ORIG_LOB_ID,
COMP_ID, SUPERCOMP, MEM_GENDER_CD, PRICING_DRG_CD, CLM_TYPE_CD, SVC_ICD9_CD, US_BILL_TYPE_CD, DISCHRG_STAT_CD, SERVICE_PAID_DT,
REF_PROV_ID, DIAG_GROUPER, DX_1_DESC, DX_2_DESC, DX_3_DESC, DX_4_DESC, DX_5_DESC, SVC_DESC, DX_6_CD, DX_7_CD, DX_8_CD, DX_9_CD,
REPORTLINE, GRP_NM, CI_CD, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, CAT_MEM_AMT, CAT_CLM_AMT, RISK_STRAT,
PORG_ID, PROV_NM, REF_PROV_NM, CATGY_DESC, CLASS_DESC, gl_no)
SELECT AUTH_ID, NEWID(), LEFT(CLM_ID,30), VEND_TYPE_CD, CLM_ADJUST_CD, LEFT(SUBR_ID,20), LEFT(PCP_ID,20), BEN_PLAN_CD, HCFA_POS_CD, CAPACITY_CD,
PORG_ID, LEFT(GRP_ID,10), LEFT(PROV_ID,20), MOD_CD, LEFT(CLM_NO,20), CLM_LINE_NO, CASE_ID, VISIT_NO, LEFT(VEND_ID,25),
LEFT(MEM_ID,20), SPEC_CD, BEN_CD, BED_TYPE_CD, PAR_FLG, DX_1_CD, DX_2_CD, DX_3_CD, DX_4_CD, DX_5_CD, DOS_DT, DOS_THRU_DT, SVC_CD,
CAP_LINE_FLG, SVC_QTY, LEFT(FEE_SCHD_ID,10), REQ_AMT, COPAY_AMT, COIN_AMT, DED_AMT, WTHD_AMT, COB_AMT, PAID_AMT, PAID_DT,
PREPAY_AMT, 'CLAIMS', GETDATE(), NULL, LEFT(GRP_ID,15), @DATE_MID, 'CLAIMS', 'CLAIMS', 'CLAIMS', 'CLAIMS', 'CLAIMS',
DATEDIFF(YEAR, DOB_DT, DOS_DT), LOB_ID, COMP_ID, 'UNASSIGNED', MEM_GENDER_CD, LEFT(PRICING_DRG_CD,3), CLM_TYPE_CD, SVC_ICD9_CD,
US_BILL_TYPE_CD, DISCHRG_STAT_CD, SERVICE_PAID_DT, LEFT(REF_PROV_ID,16), DIAG_GROUPER, DX_1_DESC, DX_2_DESC, DX_3_DESC, DX_4_DESC,
DX_5_DESC, SVC_DESC, DX_6_CD, DX_7_CD, DX_8_CD, DX_9_CD, '4 CLAIMSINCR', 'UNASSIGNED', CI_CD, 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED',
'UNASSIGNED', 'UNASSIGNED', CAT_MEM_AMT, CAT_CLM_AMT, 'UNASSIGNED', PORG_ID, 'UNASSIGNED', 'UNASSIGNED', 'CLAIMS', 'CLAIMS', gl_no
FROM AL_SOURCE_CLAIMS
WHERE DOS_DT BETWEEN @STARTDATE AND @ENDDATE
---------------------------------------- CAP INSERT ------------------------------------------------
PRINT 'CAP INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(ECL_ID, SOURCE, MEM_ID, SUBR_ID, DOS_DT, DOS_THRU_DT, PAID_DT, VEND_ID, ORIG_PROV_ID, SVC_CD, BEN_CD, REQ_AMT, PAID_AMT,
ORIG_GRP_ID, ORIG_PCP_ID, ORIG_PORG_ID, SVC_QTY, POPULATIONDATE, CAP_LINE_FLG, FEE_SCHD_ID, MIDMONTH, INC_BILLED_AMT, PROV_ID,
INC_TOT_PAID_AMT, INC_PAID_AMT, ORIG_LOB_ID, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG_1_GROUP1_DESC, DIAG1_GROUP2_CD,
DIAG_1_GROUP2_DESC, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, CATGY_CD, ORIG_BEN_PLAN_CD, COMP_ID, MEM_GENDER_CD, SUPERCOMP, DX_1_CD,
REPORTLINE, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PORG_ID, PROV_NM,
REF_PROV_NM, CATGY_DESC, CLASS_DESC, VEND_TYPE_CD, PRICING_DRG_CD, PCP_POD)
SELECT NEWID(), 'CAP', MEM_ID, MEM_ID, CAP_MO, CAP_MO, CAP_MO, CAP_VENDOR, CAP_VENDOR, LEFT(CAP_TYPE,20), LEFT(CAP_TYPE,20), PAID_AMT,
PAID_AMT, LEFT(GRP_ID,10), PCP_ID, LEFT(CAP_MEM_REG,10), 1, GETDATE(), 'Y', 'CAPITATION', @DATE_MID, PAID_AMT, 'CAP', PAID_AMT,
PAID_AMT, LOB, 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', 'CAP', '5 CAPITATION', 'CAP',
'CAP', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'CAP', LEFT(CAP_MEM_REG,10), 'UNASSIGNED', 'UNASSIGNED', 'CAP', 'CAP',
'CAP', 'CAP', 'UNASSIGNED'
FROM AL_SOURCE_CAPDNLD
WHERE CAP_MO BETWEEN @DATE_BEG AND @DATE_END
--------------------------------------------- RX INSERT -----------------------------------------------
PRINT 'RX INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(ECL_ID, SOURCE, CLM_ID, CLM_NO, PROV_ID, DOS_DT, DOS_THRU_DT, COPAY_AMT, REQ_AMT, PAID_AMT, PAID_DT, ORIG_BEN_PLAN_CD, MEM_ID,
SVC_QTY, SVC_CD, VEND_ID, ORIG_LOB_ID, ORIG_PORG_ID, ORIG_GRP_ID, ORIG_PCP_ID, BEN_CD, VEND_TYPE_CD, FEE_SCHD_ID, SUBR_ID,
CAP_LINE_FLG, POPULATIONDATE, MIDMONTH, CATGY_CD, SVC_ICD9_CD, INC_TOT_PAID_AMT, TOT_PAID_AMT, GL2_1, GL2_1_DESC, GL2_2,
GL2_2_DESC, GL2_3, INC_PAID_AMT, COMP_ID, SUPERCOMP, REPORTLINE, ANALYSISGRP, HCFA_POS_CD, SPEC_CD, GEO_REGION,
PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PORG_ID, PROV_NM, REF_PROV_NM, CATGY_DESC, CLASS_DESC, ASSIGNED_MDC_CD,
ASSIGNED_MDC_DESC, DX_1_CD, GL2_3_DESC, MEM_GENDER_CD, PRICING_DRG_CD, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC, DIAG_GROUPER,
DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, INC_BILLED_AMT, PCP_POD)
SELECT NEWID(), 'RX', MI_CLAIM_ID, LEFT(MI_CLAIM_ID,20), 'RX', MI_FILL_DT, MI_FILL_DT, MI_ACTUAL_COPAY_AMT, REQ_AMT, MI_ACTUAL_PAY_AMT,
MI_FILL_DT, MI_BENEFIT_CD, MEM_ID, MI_QTY_SUPPLY, MI_NDC, 'PBM', LOB_ID, PORG_ID, LEFT(GRP_ID,10), PCP_ID, MI_BENEFIT_CD, 'RX',
'RX', LEFT(MEM_ID,9), 'N', GETDATE(), @DATE_MID, MI_CATGY_CD, DRUG_TYPE, (MI_ACTUAL_PAY_AMT+MI_ACTUAL_COPAY_AMT),
(MI_ACTUAL_PAY_AMT+MI_ACTUAL_COPAY_AMT), '500', 'PBM DRUGS', '520', 'PBM GENERIC', 'ESI', MI_ACTUAL_PAY_AMT, 'RX', 'RX',
'6 DRUGCLAIMS', 'G', 'RX', 'RX', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'RX', LEFT(PORG_ID,10), 'UNASSIGNED',
'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', PART_B_FLG, 'RX', 'RX', 'ESI', 'RX', 'RX', 'RX', 'RX', 'RX', 'RX', 'RX', MI_ACTUAL_PAY_AMT,
'UNASSIGNED'
FROM AL_SOURCE_RX
WHERE MI_FILL_DT BETWEEN @DATE_BEG AND @DATE_END
--------------------------------------------- REV INSERT -----------------------------------------------
PRINT 'REVENUE INSERT INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO #AL_ECL_STAGING
(SOURCE, MEM_ID, DOS_DT, PAID_AMT, ORIG_GRP_ID, ORIG_PCP_ID, PORG_ID, ECL_ID, MIDMONTH, PAID_DT, SUBR_ID, INC_BILLED_AMT,
INC_TOT_PAID_AMT, INC_PAID_AMT, CAP_LINE_FLG, GL2_1, GL2_2, GL2_3, GL2_1_DESC, GL2_2_DESC, GL2_3_DESC, TOT_PAID_AMT, ORIG_LOB_ID,
FEE_SCHD_ID, ORIG_HCFA_POS_CD, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, MEM_GENDER_CD, POPULATIONDATE, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC,
DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DX_1_CD, COMP_ID, SUPERCOMP, PROV_ID, MEM_SOC_SEC_NO, REPORTLINE,
ANALYSISGRP, HCFA_POS_CD, SPEC_CD, GEO_REGION, PCP_LEAD_HOSPITAL, FACILITY_NM, RISK_STRAT, PAR_FLG, PROV_NM, REF_PROV_NM,
CATGY_DESC, CLASS_DESC, CATGY_CD, VEND_ID, VEND_TYPE_CD, PRICING_DRG_CD, DOS_THRU_DT, REQ_AMT, SVC_CD)
SELECT 'REV', REV_MEM_ID, REV_BILL_MO, REV_RATE, LEFT(REV_GROUP,10), REV_PCP, 'REV', NEWID(), @DATE_MID, REV_BILL_MO, LEFT(REV_MEM_ID,9),
REV_RATE, REV_RATE, REV_RATE, 'N', '002', '002', '002', 'REV', 'REV', 'REV', REV_RATE, REV_LOB, 'REV', 'REV', 'REV', 'REV', 'REV',
GETDATE(), 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV', MEM_SOC_SEC_NO, '3 MONREVENUE', 'REVENUE', 'REV', 'REV',
'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'UNASSIGNED', 'REV', 'UNASSIGNED', 'UNASSIGNED', 'REV', 'REV', 'REV', 'REV', 'REV', 'REV',
REV_BILL_MO, REV_RATE, REV_FILLER1
FROM AL_SOURCE_REVENUE
WHERE REV_BILL_MO BETWEEN @DATE_BEG AND @DATE_END
/* ECL LOGIC */
-- WHOLE BUNCH OF UPDATE STATEMENTS WERE HERE
PRINT 'INSERTING STAGING DATA INTO DEV_AL_ECL_TEST'
PRINT GETDATE()
INSERT INTO DEV_AL_ECL_TEST
(AUTH_ID, ANALYSISGRP, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, BED_TYPE_CD, BEN_CD, BEN_PLAN_CD
, CAP_LINE_FLG, CAPACITY_CD, CASE_ID, CAT_CLM_AMT, CAT_MEM_AMT, CATGY_CD, CATGY_DESC, CLASS_DESC
, CI_CD, CLAIM_STATUS, CLM_ADJUST_CD, CLM_ID, CLM_LINE_NO, CLM_NO, CLM_TYPE_CD, COB_AMT, COIN_AMT
, COMP_ID, COPAY_AMT, DED_AMT, DELETECOL, DENIED_CLM, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC
, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DISC_AMT, DISCHRG_STAT_CD, DIVISION_ID
, DOS_DT, DOS_DT_AGE, DOS_THRU_DT, DX_1_CD, DX_1_DESC, DX_2_CD, DX_2_DESC, DX_3_CD, DX_3_DESC
, DX_4_CD, DX_4_DESC, DX_5_CD, DX_5_DESC, DX_6_CD, DX_6_DESC, DX_7_CD, DX_7_DESC, DX_8_CD
, DX_8_DESC, DX_9_CD, DX_9_DESC, ECL_ID, FACILITY_NM, FEE_SCHD_ID, GEO_REGION, GL2_1, GL2_1_DESC
, GL2_2, GL2_2_DESC, GL2_3, GL2_3_DESC, GL2_ALL, GL2_ALL_DESC, GRP_ID, GRP_NM, HCFA_POS_CD, INC_BILLED_AMT
, INC_PAID_AMT, INC_TOT_PAID_AMT, LOB_DESC, LOB_ID, MEM_COUNTY, MEM_GENDER_CD, MEM_ID, MEM_SOC_SEC_NO
, MIDMONTH, MOD_CD, ORIG_BEN_PLAN_CD, ORIG_GRP_ID, ORIG_HCFA_POS_CD, ORIG_LOB_ID, ORIG_PCP_ID
, ORIG_PORG_ID, ORIG_PROV_ID, ORIG_SPEC_CD, PAID_AMT, PAID_DT, PAR_FLG, PCP_COUNTY, PCP_ID
, PCP_LEAD_HOSPITAL, PCP_POD, PLAN_NAME, POPULATIONDATE, PORG_ID, PORG_DESC, PREPAY_AMT
, PRICING_DRG_CD, PRICING_DRG_DESC, PROCEDURE_GROUP_1, PROCEDURE_GROUP_2, PROV_ID, PROV_NM
, REF_PROV_ID, REF_PROV_NM, REPORTLINE, REQ_AMT, REVERSED_FLG, RISK_STRAT, SERVICE_PAID_DT
, SHORT_GROUP_ID, SOURCE, SPEC_CD, SPEC_DESC, SUBR_ID, SUPERCOMP, SVC_CD, SVC_DESC, SVC_ICD9_CD
, SVC_QTY, TOT_PAID_AMT, US_BILL_TYPE_CD, VEND_ID, VEND_TYPE_CD, VISIT_NO, WTHD_AMT, MEM_ZIP
, PCP_NM, DUAL, DUAL_STATUS, ESRD, USS, INST, PROPOSED_PLAN, HOSPICE, TERMED, EXCRISK, PART_D, ECI)
SELECT AUTH_ID, ANALYSISGRP, ASSIGNED_MDC_CD, ASSIGNED_MDC_DESC, BED_TYPE_CD, BEN_CD, BEN_PLAN_CD
, CAP_LINE_FLG, CAPACITY_CD, CASE_ID, CAT_CLM_AMT, CAT_MEM_AMT, CATGY_CD, CATGY_DESC, CLASS_DESC
, CI_CD, CLAIM_STATUS, CLM_ADJUST_CD, CLM_ID, CLM_LINE_NO, CLM_NO, CLM_TYPE_CD, COB_AMT, COIN_AMT
, COMP_ID, COPAY_AMT, DED_AMT, DELETECOL, DENIED_CLM, DIAG_1_GROUP1_DESC, DIAG_1_GROUP2_DESC
, DIAG_GROUPER, DIAG1_GROUP1_CD, DIAG1_GROUP2_CD, DISC_AMT, DISCHRG_STAT_CD, DIVISION_ID
, DOS_DT, DOS_DT_AGE, DOS_THRU_DT, DX_1_CD, DX_1_DESC, DX_2_CD, DX_2_DESC, DX_3_CD, DX_3_DESC
, DX_4_CD, DX_4_DESC, DX_5_CD, DX_5_DESC, DX_6_CD, DX_6_DESC, DX_7_CD, DX_7_DESC, DX_8_CD
, DX_8_DESC, DX_9_CD, DX_9_DESC, ECL_ID, FACILITY_NM, FEE_SCHD_ID, GEO_REGION, GL2_1, GL2_1_DESC
, GL2_2, GL2_2_DESC, GL2_3, GL2_3_DESC, GL2_ALL, GL2_ALL_DESC, GRP_ID, GRP_NM, HCFA_POS_CD, INC_BILLED_AMT
, INC_PAID_AMT, INC_TOT_PAID_AMT, LOB_DESC, LOB_ID, MEM_COUNTY, MEM_GENDER_CD, MEM_ID, MEM_SOC_SEC_NO
, MIDMONTH, MOD_CD, ORIG_BEN_PLAN_CD, ORIG_GRP_ID, ORIG_HCFA_POS_CD, ORIG_LOB_ID, ORIG_PCP_ID
, ORIG_PORG_ID, ORIG_PROV_ID, ORIG_SPEC_CD, PAID_AMT, PAID_DT, PAR_FLG, PCP_COUNTY, PCP_ID
, PCP_LEAD_HOSPITAL, PCP_POD, PLAN_NAME, POPULATIONDATE, PORG_ID, PORG_DESC, PREPAY_AMT
, PRICING_DRG_CD, PRICING_DRG_DESC, PROCEDURE_GROUP_1, PROCEDURE_GROUP_2, PROV_ID, PROV_NM
, REF_PROV_ID, REF_PROV_NM, REPORTLINE, REQ_AMT, REVERSED_FLG, RISK_STRAT, SERVICE_PAID_DT
, SHORT_GROUP_ID, SOURCE, SPEC_CD, SPEC_DESC, SUBR_ID, SUPERCOMP, SVC_CD, SVC_DESC, SVC_ICD9_CD
, SVC_QTY, TOT_PAID_AMT, US_BILL_TYPE_CD, VEND_ID, VEND_TYPE_CD, VISIT_NO, WTHD_AMT, MEM_ZIP
, PCP_NM, isnull(DUAL, 'N'), DUAL_STATUS, ESRD, USS, INST, PROPOSED_PLAN, HOSPICE, TERMED, EXCRISK, PART_D, ECI
FROM #AL_ECL_STAGING
PRINT 'CLEANUP BEFORE NEXT LOOP'
COMMIT
TRUNCATE TABLE #AL_ECL_STAGING
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT
INSERT INTO build_RUN_TIME
SELECT @DATE_MID, DATEDIFF(MINUTE, @START_TIME, GETDATE()), 'DEV_AL_ECL_TEST', @RUN_MONTH, @run_time, getdate(), current_user
FETCH NEXT FROM ECL_CURS INTO @DATE_BEG,@DATE_END,@DATE_MID,@DATE_NAME
set @startdate = dateadd(month, 1, @startdate)
end
DROP TABLE #AL_R_CUST_WD_CLAIM_LINE_GL
DROP TABLE #AL_SPECIALTY
DROP TABLE #AL_GROUPER
DROP TABLE #AL_ENROLLMENT2
--DROP TABLE #AL_R_CUST_APCS_HCPCS_CODING
DROP TABLE #AL_R_CUST_ENH_CAP_GL_CODES
DROP TABLE #AL_PROVIDER
DROP TABLE #AL_R_CUST_ICD9_TO_MDC_DISTINCT
--DROP TABLE #MEM_DOB
DROP TABLE #AL_MM_ECL
DROP TABLE #MSTR_PROV_NM
DROP TABLE #MAX_DT
--DROP TABLE #DX_UPDATE
DROP TABLE #AL_ECL_STAGING
--DROP TABLE #AL_ENROLLMENT2_SOC
DROP TABLE #AL_R_CUST_DIAG_TO_MDC
DROP TABLE #AL_R_CUST_ASSIGNED_MDC_FULL_DESC
DROP TABLE #GRP_LOB
DROP TABLE #AL_R_CUST_PCP_POD
DROP TABLE #MEM_MAX_DT_PCP
--DROP TABLE #AL_LEAD_HOSPITAL
DROP TABLE #AL_SOCIAL
DROP TABLE #ENROLL_LOB
--DROP TABLE #AL_ENROLLMENT2_COUNTY
--DROP TABLE #ENROLL_LH
DROP TABLE #MAX_AL_ENROLLMENT
drop table #last_fix
drop table #providerdim
DROP TABLE #AL_REHAB_AUTHS
PRINT 'DEV_AL_ECL_TEST PROCEDURE COMPLETED:'
PRINT GETDATE()
SET @time= convert (varchar(30), getdate(), 8)
RAISERROR ('', 0, 1, @time) WITH NOWAIT
CREATE NONCLUSTERED INDEX al_ecl1_ind ON dbo.DEV_AL_ECL_TEST
(
COMP_ID ASC,
MIDMONTH ASC,
SUPERCOMP ASC,
GL2_1 ASC,
GL2_2 ASC,
LOB_ID ASC,
MEM_ID ASC,
PCP_ID ASC,
PORG_ID ASC,
PROV_ID ASC,
SOURCE ASC,
SPEC_CD ASC,
SVC_CD ASC
)
PRINT 'CREATE INDEXES:'
PRINT GETDATE()
CREATE NONCLUSTERED INDEX AL_ECL2_IND ON dbo.DEV_AL_ECL_TEST
(
[PCP_POD] ASC,
[ECL_ID] ASC,
[VEND_ID] ASC,
[MEM_ID] ASC,
[COMP_ID] ASC,
[MIDMONTH] ASC,
[SUPERCOMP] ASC,
[SOURCE] ASC,
[GL2_1_DESC] ASC,
[GL2_2_DESC] ASC,
[GL2_3_DESC] ASC
)
INCLUDE ( [AUTH_ID],
[CLM_NO],
[DOS_DT],
[DOS_THRU_DT],
[DX_1_CD],
[DX_1_DESC],
[GRP_ID],
[HCFA_POS_CD],
[INC_PAID_AMT],
[LOB_ID],
[MEM_COUNTY],
[MOD_CD],
[PAID_AMT],
[PAID_DT],
[PCP_ID],
[PORG_ID],
[PRICING_DRG_CD],
[PROV_ID],
[PROV_NM],
[SPEC_DESC],
[SVC_CD],
[SVC_DESC],
[SVC_QTY],
[PCP_NM],
[DUAL]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
PRINT GETDATE()
END /* MAIN */
Trisha
