Procedure gives wrong information

Procedure gives wrong information

I have a stored procedure that Is not working and I can't seem to find out
what I need to do to fix it. What is happening is I have a provider that
keeps on coming up with data that should not be there. The data in the
rpt_dat_ChgDetail,rptdata_xl.dbo.Rpts_SubDict,rpt_dic_CPT tables are
accurate.
The data in the rptdata_xl.dbo.RptSrc_ChgDetail in the qrptpd is
inaccurate. Any help is appreciated.
CREATE TABLE tmpRpt_ChgDetail (qrptid int,curpd int,uci varchar(10),clntid
int,monasdt datetime
,grp1 int,grp1_id varchar(50),grp2 int,grp2_id
varchar(50),filt1 int
,filt1_id varchar(50),filt2 int,filt2_id varchar(50),aid int
,PatName varchar(75),AcctNu varchar(50),eid int,slid int,dos
datetime
,dosmonth datetime,chgpostdt datetime,chgpostmonth
datetime,cptcode varchar(50)
,cptcomp varchar(1),cptdisplay varchar(52),mod1
varchar(50),mod2 varchar(50)
,mod3 varchar(50),mod4 varchar(50),diag1 varchar(50),diag2
varchar(50)
,diag3 varchar(50),diag4 varchar(50),insmne
varchar(50),insdesc varchar(50)
,ticketnum varchar(500),totunit int,adjunit int,chgamt
decimal(18, 2)
,cptdsc varchar(255) );
-- Declare Bulk Insert
DECLARE bulk_insert CURSOR FOR
-- Get a list of the files to bulk insert with fields for names
SELECT qrptid,rptid
FROM tmp_RptUpdtQ
WHERE srctable = 'RptSrc_ChgDetail'
ORDER BY qrptid;
-- Start Loop
OPEN bulk_insert
FETCH next FROM bulk_insert INTO @qid,@rptid
WHILE @@fetch_status = 0
BEGIN
-- SQL
INSERT INTO tmpRpt_ChgDetail
(qrptid,curpd,uci,clntid,monasdt,grp1,grp1_id,grp2,grp2_id,filt1
,filt1_id,filt2,filt2_id,aid,PatName,AcctNu,eid,slid,dos,dosmonth,chgpostdt
,chgpostmonth,cptcode,cptcomp,cptdisplay,mod1,mod2,mod3,mod4,diag1,diag2
,diag3,diag4,insmne,insdesc,ticketnum,totunit,adjunit,chgamt,cptdsc
)
SELECT uq.qrptid,uq.rptpd,uq.uci,chg.clntid,chg.reportmonth
,uq.grp1,(case when uq.grp1 = 2 then CAST(chg.provid AS varchar(50))
when uq.grp1 = 3 then
CAST(chg.facid AS varchar(50))
when uq.grp1 = 4 then
CAST(chg.posid AS varchar(50))
when uq.grp1 = 5 then
CAST(chg.modalid AS
varchar(50))
when uq.grp1 = 6 then
CAST(chg.dptid AS varchar(50))
when uq.grp1 = 7 then
isnull(ins.rcat,'SP')
else '0' end),uq.grp2
,(case when uq.grp2 = 2 then CAST(chg.provid AS varchar(50))
when uq.grp2 = 3 then
CAST(chg.facid AS varchar(50))
when uq.grp2 = 4 then
CAST(chg.posid AS varchar(50))
when uq.grp2 = 5 then
CAST(chg.modalid AS
varchar(50))
when uq.grp2 = 6 then
CAST(chg.dptid AS varchar(50))
when uq.grp2 = 7 then
isnull(ins.rcat,'SP')
else '0' end),uq.filt1
,(case when uq.filt1 = 2 then CAST(chg.provid AS varchar(50))
when uq.filt1 = 3 then
CAST(chg.facid AS varchar(50))
when uq.filt1 = 4 then
CAST(chg.posid AS varchar(50))
when uq.filt1 = 5 then
CAST(chg.modalid AS
varchar(50))
when uq.filt1 = 6 then
CAST(chg.dptid AS varchar(50))
when uq.filt1 = 7 then
isnull(ins.rcat,'SP')
else '0' end),uq.filt2
,(case when uq.filt2 = 2 then CAST(chg.provid AS varchar(50))
when uq.filt2 = 3 then
CAST(chg.facid AS varchar(50))
when uq.filt2 = 4 then
CAST(chg.posid AS varchar(50))
when uq.filt2 = 5 then
CAST(chg.modalid AS
varchar(50))
when uq.filt2 = 6 then
CAST(chg.dptid AS varchar(50))
when uq.filt2 = 7 then
isnull(ins.rcat,'SP')
else '0' end)
,chg.aid,pat.PatName,pat.AcctNu,chg.eid,chg.slid
,chg.dos,chg.dosmonth,chg.chgpostdate,chg.chgpostmonth,chg.cptcode,chg.cptcomp
,chg.cptdisplay,chg.mod1,chg.mod2,chg.mod3,chg.mod4,chg.diag1,chg.diag2,chg.diag3
,chg.diag4,chg.priminsmne,ins.insdesc,chg.ticketnum,chg.totunit,chg.adjunit,chg.chgamt,'CPT'
FROM rpt_dat_ChgDetail chg
INNER JOIN tmp_RptUpdtQ uq ON chg.clntid = uq.clntid
LEFT JOIN rpt_dic_Ins ins ON chg.clntid = ins.clntid AND chg.priminsmne =
ins.insmne
LEFT JOIN rptdata_ahs.dbo.bi_PatientData pat ON chg.clntid = pat.clntid
AND chg.aid = pat.aid
WHERE (uq.qrptid = @qid);
-- Build Filter Join Tables
-- Filter 1
SELECT uq.qrptid,uq.filt1,isnull(f1.id,'0') as fid
INTO tmpRpt_Filt1
FROM tmp_RptUpdtQ uq
LEFT JOIN rptq_rpts_filt1 f1 ON uq.qrptid = f1.qrptid
ORDER BY uq.qrptid,uq.filt1,isnull(f1.id,'0');
-- Filter 2
SELECT uq.qrptid,uq.filt2,isnull(f2.id,'0') as fid
INTO tmpRpt_Filt2
FROM tmp_RptUpdtQ uq
LEFT JOIN rptq_rpts_filt2 f2 ON uq.qrptid = f2.qrptid
ORDER BY uq.qrptid,uq.filt2,isnull(f2.id,'0');
-- Clear Zero Records
DELETE FROM tmpRpt_ChgDetail WHERE ((totunit = 0) AND (adjunit = 0) AND
(chgamt = 0));
-- Get CPT Description master list
SELECT cptcode,Max(cptdesc) as dsc
INTO tmp_ChgDetail_CPTDescFix
FROM rpt_dic_CPT
GROUP BY cptcode
ORDER BY cptcode;
-- Update in Table from dictionary
UPDATE tmpRpt_ChgDetail
SET cptdsc = dic.cptdesc
FROM tmpRpt_ChgDetail tmp
INNER JOIN rpt_dic_CPT dic ON tmp.clntid = dic.clntid AND tmp.cptcode =
dic.cptcode
AND tmp.cptcomp = dic.cptcomp;
-- Update any missing
UPDATE tmpRpt_ChgDetail
SET cptdsc = dic.dsc
FROM tmpRpt_ChgDetail tmp
INNER JOIN tmp_ChgDetail_CPTDescFix dic ON tmp.cptcode = dic.cptcode
WHERE cptdsc = 'CPT';
-- Drop Temp
DROP TABLE tmp_ChgDetail_CPTDescFix;
-- Post Live
INSERT INTO rptdata_xl.dbo.RptSrc_ChgDetail
(qrptid,qrptpd,clntid,uci,monasdt,grpdsc1
,grpdsc2,aid,PatName,AcctNu,eid,slid,dos,dosmonth,chgpostdate,chgpostmonth
,cptcode,cptcomp,cptdisplay,cptdsc,mod1,mod2,mod3,mod4,diag1,diag2,diag3,diag4
,priminsmne,priminsdesc,ticketnum,totunit,adjunit,chgamt )
SELECT
t.qrptid,t.curpd,t.clntid,t.uci,t.monasdt,isnull(g1.dsc,'NONE'),isnull(g2.dsc,'NONE')
,t.aid,t.PatName,t.AcctNu,t.eid,t.slid,t.dos,t.dosmonth,t.chgpostdt,t.chgpostmonth
,t.cptcode,t.cptcomp,t.cptdisplay,t.cptdsc,t.mod1,t.mod2,t.mod3,t.mod4,t.diag1,t.diag2
,t.diag3,t.diag4,t.insmne,t.insdesc,t.ticketnum,t.totunit,t.adjunit,t.chgamt
FROM tmpRpt_ChgDetail t
LEFT JOIN rptdata_xl.dbo.Rpts_SubDict g1 ON t.clntid = g1.clntid AND
t.grp1 = g1.grp
AND t.grp1_id = g1.id
LEFT JOIN rptdata_xl.dbo.Rpts_SubDict g2 ON t.clntid = g2.clntid AND
t.grp2 = g2.grp
AND t.grp2_id = g2.id
INNER JOIN tmpRpt_Filt1 f1 ON t.qrptid = f1.qrptid AND t.filt1 = f1.filt1
AND t.filt1_id = f1.fid
INNER JOIN tmpRpt_Filt2 f2 ON t.qrptid = f2.qrptid AND t.filt2 = f2.filt2
AND t.filt2_id = f2.fid
ORDER BY t.qrptid,isnull(g1.dsc,'NONE'),isnull(g2.dsc,'NONE');
-- Clear Temp Table
TRUNCATE TABLE tmpRpt_ChgDetail;
DROP TABLE tmpRpt_filt1;
DROP TABLE tmpRpt_filt2;
-- Loop to Next
FETCH next FROM bulk_insert INTO @qid,@rptid -- Next Record
END
-- Close Bulk Insert
CLOSE bulk_insert
DEALLOCATE bulk_insert
-- Drop Temp Tables
DROP TABLE tmpRpt_ChgDetail;