FrankPhillips
IS-IT--Management
I have a stored procedure i need to use for a report. however, when i select this stored procedure, no fields show up in the field explorer.
This stored procedure runs fine, and gives me the correct output; I've read that Crystal reports can handle temp tables per their own website. However, for the life of me I cannot access the fields.
Im at a loss since I have some fields that are created on the fly based on data from multiple tables.
Any help would be appreciate any suggestions. If its something with the stored procedure itself, cr is reallllly picky because as i said, this works anyplace else.
Thanks
Frank
This is my stored procedure:
CREATE PROCEDURE sp_PracticeBatch @BatchID int, @PracticeID int AS
CREATE TABLE #AllClaims (
[claim_type] [varchar] (1) ,
[ClaimID] [int],
[prac_id] [int] NOT NULL ,
[prov_id] [int] NOT NULL ,
[loc_id] [int] NOT NULL ,
[region_id] [int] NOT NULL ,
[employer_id] [int] NOT NULL ,
[employee_id] [int] NOT NULL ,
[patient_id] [int] NOT NULL ,
[plan_id] [int] NOT NULL ,
[claim_date] [datetime] NOT NULL ,
[par_claim] [varchar] (1) ,
[non_par_fee] [money] NULL ,
[pre_claim_used] [money] NULL ,
[pre_claim_remain] [money] NULL ,
[employer_liab] [money] NULL ,
[employee_liab] [money] NULL ,
[ddn_fee] [money] NULL ,
[other_ins_payment] [money] NULL ,
[remit_amount] [money] NULL ,
[claim_status] [int] NULL ,
[emp_batch_id] [int] NULL ,
[prac_batch_id] [int] NULL ,
[remit_date] [datetime] NULL
)
if @PracticeID = 0
begin
insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)
(select
'D',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from claimmaster C
where C.prac_batch_id = @BatchID)
insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)
(select
'O',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from Orthomaster C
where C.prac_batch_id = @BatchID)
select distinct C.*,D.*, P.* ,Q.*
from #AllClaims C, dependants D , Providers P, Practice_Master Q
where
C.prov_id = P.pro_id and
C.patient_id = D.de_id and
C.prac_id = Q.pr_id and
C.prac_batch_id = @BatchID
order by C.claim_date
end
else
begin
insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)
(select
'D',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from claimmaster C
where C.prac_batch_id = @BatchID and C.prac_batch_id = @PracticeID)
insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)
(select
'O',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from Orthomaster C
where C.prac_batch_id = @BatchID and C.prac_batch_id = @PracticeID)
select distinct C.*,D.*, P.* ,Q.*
from #AllClaims C, dependants D , Providers P, Practice_Master Q
where
C.prov_id = P.pro_id and
C.patient_id = D.de_id and
C.prac_id = Q.pr_id and
C.prac_batch_id = @BatchID and
c.prac_id = @PracticeID
order by C.claim_date
end
GO