This is the code in step 3
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_Infmtx_003_MonthlyAR]
AS
--- Added 4 Drop table code to avoid duplication 9/11/2013 TD
IF OBJECT_ID('tmp_ARDetail') is not null
BEGIN
DROP TABLE tmp_ARDetail;
END
IF OBJECT_ID('tmp_AR') is not null
BEGIN
DROP TABLE tmp_AR;
END
IF OBJECT_ID('tmp_CalcAR') is not null
BEGIN
DROP TABLE tmp_CalcAR;
END
IF OBJECT_ID('tmp_ARCalc') is not null
BEGIN
DROP TABLE tmp_ARCalc;
END
/************************************************
*** UPDATE THESE VARIABLES BEFORE PROCESSING ***
***********************************************/
-- Set Variables
DECLARE @curpd int,@agingdt as datetime
SET @curpd = 380
SET @agingdt = '9/4/2013'
/******************
*** END UPDATE ***
*****************/
/* CALCULATE BEGINNING A/R */
-- Post Data
SELECT
'ARC_Infmtx' as UCI
,chgid as ChgID
,dat.doschg as DosDt
,dat.postdtchg as ChgPostDt
,@curpd as arpdid
,dat.cptcode as CptCode
,CAST('PROCEDURE' as varchar(255)) as CptDesc
,dat.cptcomp as CptComp
,isnull(bprv.provname_lf,'UNAPPLIED BILLING PROVIDER') as BillingProvider
,isnull(rprv.provname_lf,'UNAPPLIED RENDERING PROVIDER') as RenderingProvider
,isnull(fac.facdesc,'UNAPPLIED FACILITY') as Facility
,isnull(pos.posdesc,'UNAPPLIED PLACE OF SERVICE') as PlaceOfService
,isnull(dpt.dptdesc,'UNAPPLIED DEPARTMENT') as Department
,isnull(modal.modaldesc,'UNAPPLIED MODALITY') as Modality
,isnull(ref.refname_lf,'UNAPPLIED REF PROVIDER') as ReferringProvider
,(case when dat.curinsmne = 'Guarantor' then 'SELFPAY'
else isnull(cptyp.ptypemne,'UNAPPLIED PTYPE') end) as PType
,(case when dat.trantype = 1 then dat.amt else 0 end) as ChgAmt
,(case when (dat.trantype = 4 and trncd.pmtcat = 1) then dat.amt else 0 end) as PmtGuarAmt
,(case when (dat.trantype = 4 and trncd.pmtcat <> 1) then dat.amt else 0 end) as PmtInsAmt
,(case when (dat.trantype = 3 and dat.crcat = 1) then dat.amt else 0 end) as AdjContrAmt
,(case when (dat.trantype = 3 and dat.crcat <> 1) then dat.amt else 0 end) as AdjWOAmt
,(case when Len(LTrim(RTrim(ref.refname_assoc))) > 0
then ref.refname_assoc
else 'NO REFERRING GROUP' end) as RefGroup
INTO tmp_ARDetail
FROM infmtx_dat_Transactions dat
LEFT JOIN infmtx_dic_Provider bprv on dat.billprov = bprv.provid
LEFT JOIN infmtx_dic_Provider rprv on dat.rendprov = rprv.provid
LEFT JOIN infmtx_dic_Facility fac on dat.facid = fac.facid
LEFT JOIN infmtx_dic_POS pos on dat.posid = pos.posid
LEFT JOIN infmtx_dic_Department dpt on dat.dptid = dpt.dptid
LEFT JOIN infmtx_dic_Modality modal on dat.modalid = modal.modalid
LEFT JOIN infmtx_dic_RefProvider ref on dat.refprovid = ref.refid
LEFT JOIN infmtx_dic_TransCode trncd on dat.trantype = trncd.trantype
AND dat.transcode = trncd.trancodemne
LEFT JOIN infmtx_dic_Insurance cins on dat.curinsmne = cins.insmne
LEFT JOIN infmtx_dic_PType cptyp on cins.irptcat = cptyp.ptypemne
WHERE dat.rptpd < @curpd;
-- Set CPT Description
UPDATE tmp_ARDetail
SET CptDesc = cpt.cptdesc
FROM tmp_ARDetail ar
INNER JOIN infmtx_dic_CPT cpt ON ar.cptcode = cpt.cptcode AND ar.cptcomp = cpt.cptcomp;
-- Summarize on Charge Level
SELECT
tmp.UCI
,tmp.DosDt
,tmp.ChgPostDt
,tmp.arpdid
,tmp.CptCode
,tmp.CptDesc
,tmp.CptComp
,tmp.BillingProvider
,tmp.RenderingProvider
,tmp.Facility
,tmp.PlaceOfService
,tmp.Department
,tmp.Modality
,tmp.ReferringProvider
,tmp.PType
,Sum(tmp.ChgAmt) as Charges
,Sum(tmp.PmtGuarAmt) as GuarantorPmts
,Sum(tmp.PmtInsAmt) as InsurancePmts
,Sum(tmp.AdjContrAmt) as ContrAdjs
,Sum(tmp.AdjWOAmt) as WriteOffs
,CAST(0 as decimal(18,2)) as CurBalance
,@agingdt as AgeDt
,0 as DOSDys
,0 as AgeBucketInt
,'000_000_Days' as AgeBucketString
,'N' as Over90DaysFlag
,'N' as Over120DaysFlag
,tmp.RefGroup
INTO tmp_AR
FROM tmp_ARDetail tmp
GROUP BY tmp.UCI,tmp.DosDt,tmp.ChgPostDt,tmp.arpdid,tmp.CptCode,tmp.CptDesc,tmp.CptComp
,tmp.BillingProvider,tmp.RenderingProvider,tmp.Facility,tmp.PlaceOfService
,tmp.Department,tmp.Modality,tmp.ReferringProvider,tmp.PType,tmp.RefGroup;
-- Drop Temp Table
DROP TABLE tmp_ARDetail;
/*** Calculate Current Balance and remove Zero balances ***/
-- Current Balance
UPDATE tmp_AR SET CurBalance = (Charges - GuarantorPmts - InsurancePmts - ContrAdjs - WriteOffs);
-- Delete Zero Records
DELETE FROM tmp_AR WHERE CurBalance = 0;
--select Sum(CurBalance) from tmp_AR;
/*** Calculate Aging Data ***/
-- Calculate Days
UPDATE tmp_AR SET DOSDys = isnull(CAST((AgeDt - DosDt) as int),0);
-- Set Bucket
UPDATE tmp_AR SET AgeBucketInt = (case when DOSDys < 31 then 0
when (DOSDys > 30 and DOSDys < 61) then 1
when (DOSDys > 60 and DOSDys < 91) then 2
when (DOSDys > 90 and DOSDys < 121) then 3
when (DOSDys > 120 and DOSDys < 151) then 4
when (DOSDys > 150 and DOSDys < 181) then 5
else 6 end);
-- Set Bucket Description
UPDATE tmp_AR SET AgeBucketString = (case when AgeBucketInt = 0 then '000_030_Days'
when AgeBucketInt = 1 then '031_060_Days'
when AgeBucketInt = 2 then '061_090_Days'
when AgeBucketInt = 3 then '091_120_Days'
when AgeBucketInt = 4 then '121_150_Days'
when AgeBucketInt = 5 then '151_180_Days'
else '181_Over' end);
-- Set Over 90 and Over 120 Flags
UPDATE tmp_AR SET Over90DaysFlag = 'Y' WHERE AgeBucketInt > 2;
UPDATE tmp_AR SET Over120DaysFlag = 'Y' WHERE AgeBucketInt > 3;
-- Post to Calc Table as Begining Balance
SELECT
ar.UCI
,pd.monasdt as BillMonth
,pd.billpddiff as BillMonthDiff
,pd.fy as BillYear
,pd.fypddiff as BillYearDiff
,ar.CptCode
,ar.CptDesc
,ar.CptComp
,ar.BillingProvider
,ar.RenderingProvider
,ar.Facility
,ar.PlaceOfService
,ar.Department
,ar.Modality
,ar.ReferringProvider
,ar.PType
,(CAST(ar.CurBalance as decimal(38,2))) as BegAR
,(CAST(0 as decimal(38,2))) as Charges
,(CAST(0 as decimal(38,2))) as Payments_Guar
,(CAST(0 as decimal(38,2))) as Payments_Ins
,(CAST(0 as decimal(38,2))) as Adjustments_Contr
,(CAST(0 as decimal(38,2))) as Adjustments_WriteOffs
,(CAST(0 as decimal(38,2))) CurrentBalance
,ar.AgeBucketInt as Bucket_Number
,ar.AgeBucketString as Bucket_Desc
,ar.Over90DaysFlag
,ar.Over120DaysFlag
,ar.RefGroup
INTO tmp_CalcAR
FROM tmp_AR ar
LEFT JOIN Master_dic_Period pd ON ar.arpdid = pd.pd;
-- Drop Temp Table
DROP TABLE tmp_AR;
/*** POST CURRENT MONTH DATA ***/
-- Post Data
SELECT
'ARC_Infmtx' as UCI
,chgid as ChgID
,dat.doschg as DosDt
,dat.postdtchg as ChgPostDt
,@curpd as arpdid
,isnull(cpt.cptcode,'ZYZYZ') as CptCode
,isnull(cpt.cptdesc,'UNAPPLIED CPT') as CptDesc
,isnull(cpt.cptcomp,'G') as CptComp
,isnull(bprv.provname_lf,'UNAPPLIED BILLING PROVIDER') as BillingProvider
,isnull(rprv.provname_lf,'UNAPPLIED RENDERING PROVIDER') as RenderingProvider
,isnull(fac.facdesc,'UNAPPLIED FACILITY') as Facility
,isnull(pos.posdesc,'UNAPPLIED PLACE OF SERVICE') as PlaceOfService
,isnull(dpt.dptdesc,'UNAPPLIED DEPARTMENT') as Department
,isnull(modal.modaldesc,'UNAPPLIED MODALITY') as Modality
,isnull(ref.refname_lf,'UNAPPLIED REF PROVIDER') as ReferringProvider
,(case when dat.curinsmne = 'Guarantor' then 'SELFPAY'
else isnull(cptyp.ptypemne,'UNAPPLIED PTYPE') end) as PType
,(case when dat.trantype = 1 then dat.amt else 0 end) as ChgAmt
,(case when (dat.trantype = 4 and trncd.pmtcat = 1) then dat.amt else 0 end) as PmtGuarAmt
,(case when (dat.trantype = 4 and trncd.pmtcat <> 1) then dat.amt else 0 end) as PmtInsAmt
,(case when (dat.trantype = 3 and dat.crcat = 1) then dat.amt else 0 end) as AdjContrAmt
,(case when (dat.trantype = 3 and dat.crcat <> 1) then dat.amt else 0 end) as AdjWOAmt
,(case when Len(LTrim(RTrim(ref.refname_assoc))) > 0
then ref.refname_assoc
else 'NO REFERRING GROUP' end) as RefGroup
INTO tmp_ARDetail
FROM infmtx_dat_Transactions dat
LEFT JOIN infmtx_dic_CPT cpt on dat.cptid = cpt.cptid
LEFT JOIN infmtx_dic_Provider bprv on dat.billprov = bprv.provid
LEFT JOIN infmtx_dic_Provider rprv on dat.rendprov = rprv.provid
LEFT JOIN infmtx_dic_Facility fac on dat.facid = fac.facid
LEFT JOIN infmtx_dic_POS pos on dat.posid = pos.posid
LEFT JOIN infmtx_dic_Department dpt on dat.dptid = dpt.dptid
LEFT JOIN infmtx_dic_Modality modal on dat.modalid = modal.modalid
LEFT JOIN infmtx_dic_RefProvider ref on dat.refprovid = ref.refid
LEFT JOIN infmtx_dic_TransCode trncd on dat.trantype = trncd.trantype
AND dat.transcode = trncd.trancodemne
LEFT JOIN infmtx_dic_Insurance cins on dat.curinsmne = cins.insmne
LEFT JOIN infmtx_dic_PType cptyp on cins.irptcat = cptyp.ptypemne
WHERE dat.rptpd = @curpd;
-- Summarize on Charge Level
SELECT
tmp.UCI
,tmp.DosDt
,tmp.ChgPostDt
,tmp.arpdid
,tmp.CptCode
,tmp.CptDesc
,tmp.CptComp
,tmp.BillingProvider
,tmp.RenderingProvider
,tmp.Facility
,tmp.PlaceOfService
,tmp.Department
,tmp.Modality
,tmp.ReferringProvider
,tmp.PType
,Sum(tmp.ChgAmt) as Charges
,Sum(tmp.PmtGuarAmt) as GuarantorPmts
,Sum(tmp.PmtInsAmt) as InsurancePmts
,Sum(tmp.AdjContrAmt) as ContrAdjs
,Sum(tmp.AdjWOAmt) as WriteOffs
,CAST(0 as decimal(18,2)) as CurBalance
,@agingdt as AgeDt
,0 as DOSDys
,0 as AgeBucketInt
,'000_000_Days' as AgeBucketString
,'N' as Over90DaysFlag
,'N' as Over120DaysFlag
,tmp.RefGroup
INTO tmp_AR
FROM tmp_ARDetail tmp
GROUP BY tmp.UCI,tmp.DosDt,tmp.ChgPostDt,tmp.arpdid,tmp.CptCode,tmp.CptDesc,tmp.CptComp
,tmp.BillingProvider,tmp.RenderingProvider,tmp.Facility,tmp.PlaceOfService
,tmp.Department,tmp.Modality,tmp.ReferringProvider,tmp.PType,tmp.RefGroup;
-- Drop Temp Table
DROP TABLE tmp_ARDetail;
-- Calculate Aging Data
-- Calculate Days
UPDATE tmp_AR SET DOSDys = isnull(CAST((AgeDt - DosDt) as int),0);
-- Set Bucket
UPDATE tmp_AR SET AgeBucketInt = (case when DOSDys < 31 then 0
when (DOSDys > 30 and DOSDys < 61) then 1
when (DOSDys > 60 and DOSDys < 91) then 2
when (DOSDys > 90 and DOSDys < 121) then 3
when (DOSDys > 120 and DOSDys < 151) then 4
when (DOSDys > 150 and DOSDys < 181) then 5
else 6 end);
-- Set Bucket Description
UPDATE tmp_AR SET AgeBucketString = (case when AgeBucketInt = 0 then '000_030_Days'
when AgeBucketInt = 1 then '031_060_Days'
when AgeBucketInt = 2 then '061_090_Days'
when AgeBucketInt = 3 then '091_120_Days'
when AgeBucketInt = 4 then '121_150_Days'
when AgeBucketInt = 5 then '151_180_Days'
else '181_Over' end);
-- Set Over 90 and Over 120 Flags
UPDATE tmp_AR SET Over90DaysFlag = 'Y' WHERE AgeBucketInt > 2;
UPDATE tmp_AR SET Over120DaysFlag = 'Y' WHERE AgeBucketInt > 3;
-- Post to Calc Table
INSERT INTO tmp_CalcAR (UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp
,BillingProvider,RenderingProvider,Facility,PlaceOfService,Department
,Modality,ReferringProvider,PType,BegAR,Charges,Payments_Guar
,Payments_Ins,Adjustments_Contr,Adjustments_WriteOffs,CurrentBalance
,Bucket_Number,Bucket_Desc,Over90DaysFlag,Over120DaysFlag,RefGroup )
SELECT
ar.UCI
,pd.monasdt
,pd.billpddiff
,pd.fy
,pd.fypddiff
,ar.CptCode
,ar.CptDesc
,ar.CptComp
,ar.BillingProvider
,ar.RenderingProvider
,ar.Facility
,ar.PlaceOfService
,ar.Department
,ar.Modality
,ar.ReferringProvider
,ar.PType
,(CAST(0 as decimal(38,2))) as BegAR
,(CAST(ar.Charges as decimal(38,2)))
,(CAST(ar.GuarantorPmts as decimal(38,2)))
,(CAST(ar.InsurancePmts as decimal(38,2)))
,(CAST(ar.ContrAdjs as decimal(38,2)))
,(CAST(ar.WriteOffs as decimal(38,2)))
,(CAST(0 as decimal(38,2)))
,ar.AgeBucketInt
,ar.AgeBucketString
,ar.Over90DaysFlag
,ar.Over120DaysFlag
,ar.RefGroup
FROM tmp_AR ar
LEFT JOIN Master_dic_Period pd ON ar.arpdid = pd.pd;
-- Drop Temp Table
DROP TABLE tmp_AR;
-- Summarize AR Calc
SELECT
UCI
,BillMonth
,BillMonthDiff
,BillYear
,BillYearDiff
,CptCode
,CptDesc
,CptComp
,BillingProvider
,RenderingProvider
,Facility
,PlaceOfService
,Department
,Modality
,ReferringProvider
,PType
,Sum(BegAR) as B_AR
,Sum(Charges) as Chgs
,Sum(Payments_Guar) as GuarPmts
,Sum(Payments_Ins) as InsPmts
,Sum(Adjustments_Contr) as ContrAdjs
,Sum(Adjustments_WriteOffs) as WOAdjs
,Sum(CurrentBalance) as CurBal
,Bucket_Number
,Bucket_Desc
,Over90DaysFlag
,Over120DaysFlag
,RefGroup
INTO tmp_ARCalc
FROM tmp_CalcAR
GROUP BY UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp,BillingProvider
,RenderingProvider,Facility,PlaceOfService,Department,Modality,ReferringProvider
,PType,Bucket_Number,Bucket_Desc,Over90DaysFlag,Over120DaysFlag,RefGroup;
-- Drop Temp Table
DROP TABLE tmp_CalcAR;
-- Calculate Ending A/R
UPDATE tmp_ARCalc SET CurBal = (B_AR + Chgs - GuarPmts - InsPmts - ContrAdjs - WOAdjs);
-- Delete Zero Records
DELETE FROM tmp_ARCalc WHERE CurBal = 0;
-- Post to Live Table
INSERT INTO rpt_Infmtx_MonthlyAR (UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CPTCode,CPTDesc
,CPTComp,BillingProvider,RenderingProvider,Facility,PlaceOfService
,Department,Modality,ReferringProvider,PType,BeginningAR,Charges
,GuarantorPayments,InsurancePayments,ContractualAdjustments
,WriteOffAdjustments,EndingAR,AgeBucket_Number,AgeBucket_Desc
,Over90Flag,Over120Flag,RefGroup )
SELECT UCI,BillMonth,BillMonthDiff,BillYear,BillYearDiff,CptCode,CptDesc,CptComp,BillingProvider
,RenderingProvider,Facility,PlaceOfService,Department,Modality,ReferringProvider,PType
,B_AR,Chgs,GuarPmts,InsPmts,ContrAdjs,WOAdjs,CurBal,Bucket_Number,Bucket_Desc,Over90DaysFlag
,Over120DaysFlag,RefGroup
FROM tmp_ARCalc;
-- Drop Temp Table
DROP TABLE tmp_ARCalc;
/**** Clear out a month previously posted
DELETE FROM rpt_Infmtx_MonthlyAR WHERE BillMonth = '11/1/2012';
***/