OK. It is still not working no matter what i do. I still get some returns where the proc_cd is not null. It will return all the claim lines but then some proc_cd have a value. I think I need to put it in the main query just not sure where. If I put the unet.proc_cd is null or = '' within the where statement it will still return claims and claim lines that have proc_cds cuz it is within the subquery. If i put it in the first query I think it will work but not sure where to put it.
proc sql;
create table auth.claims as
select * from connection to db2
(select
a.clm_aud_nbr,
mbr_sys_id,
fst_srvc_dt,
adj_hccc_cd,
prov_tin,
proc_cd,
compat_cd,
alloc_rvnu_cd,
mpin,
full_nm,
finc_arng_cd,
finc_arng_desc,
AMA_PL_OF_SRVC_CD,
ama_pl_of_srvc_desc,
member_mkt,
Provider_mkt,
Prov_Status, /*added just in to QC*/
FACL_TYP_TXT,
sum(epd_calc_allw_amt) as epd_calc_allw_amt,
sum(alloc_allw_amt) as alloc_allw_amt,
sum(alloc_sbmt_chrg_amt) as alloc_sbmt_chrg_amt,
sum(alloc_net_pd_amt) as alloc_net_pd_amt
from
(SELECT
unet.clm_aud_nbr,
unet.mbr_sys_id,
unet.fst_srvc_dt,
unet.adj_hccc_cd,
unet.prov_tin,
unet.proc_cd,
unet.compat_cd,
unet.alloc_rvnu_cd,
prov.mpin,
prov.full_nm,
co.finc_arng_cd,
co.finc_arng_desc,
plc.AMA_PL_OF_SRVC_CD,
plc.ama_pl_of_srvc_desc,
market.mkt_nm as member_mkt,
MARKET2.MKT_NM as Provider_mkt,
unet.prov_prtcp_sts_cd as Prov_Status, /***added just in to QC***/
BILL.FACL_TYP_TXT,
epd_calc_allw_amt,
alloc_allw_amt,
alloc_sbmt_chrg_amt,
alloc_net_pd_amt
from
galaxy.unet_ub92_revenue_code_statistical_service unet
inner join galaxy.unet_claim_header head
on unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id
and
unet.mbr_sys_id = head.mbr_sys_id
inner join galaxy.provider prov
on unet.srvc_prov_row_eff_dt = prov.prov_row_eff_dt
and
unet.srvc_prov_sys_id = prov.prov_sys_id
inner join galaxy.company_code co
on head.co_cd = co.co_cd
inner join galaxy.place_of_service_code plc
on unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id
inner join galaxy.unet_market market
on head.geo_area_nbr = market.mkt_nbr
and
head.geo_area_nbr_mkt_row_eff_dt = market.mkt_row_eff_dt
inner join galaxy.UNET_FACILITY_UB92 ub92
on head.mbr_sys_id=ub92.mbr_sys_id
and
head.UNET_CLM_HEAD_SYS_ID =ub92.UNET_CLM_HEAD_SYS_ID
INNER JOIN Galaxy.COSMOS_BILL_TYPE_CODE BILL
ON ub92.BIL_TYP_CD = BILL.BIL_TYP_CD
INNER JOIN galaxy.UNET_MARKET MARKET2
on unet.PROV_CONTR_MKT_NBR = MARKET2.MKT_NBR
and
unet.PROV_CONTR_MKT_ROW_EFF_DT = MARKET2.MKT_ROW_EFF_DT )a
inner join
(select distinct unet.clm_aud_nbr from
galaxy.unet_ub92_revenue_code_statistical_service unet
inner join galaxy.unet_claim_header head
on unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id
and
unet.mbr_sys_id = head.mbr_sys_id
inner join galaxy.company_code co
on head.co_cd = co.co_cd
inner join galaxy.product prod
on head.prdct_cd = prod.prdct_cd
inner join galaxy.business_line_code blc
on prod.bus_line_cd = blc.bus_line_cd
inner join galaxy.member_coverage_month mem
on unet.mbr_sys_id = mem.mbr_sys_id
and unet.fst_srvc_dt between mem.MBR_COV_MO_ROW_eff_DT
and mem.MBR_COV_MO_ROW_END_DT
inner join galaxy.place_of_service_code plc
on unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id
where
unet.alloc_rvnu_cd in &rev
and
co.finc_arng_cd in ('F','A')
and
mem.cob_ind = ('N')
and
plc.AMA_PL_OF_SRVC_CD='22'
and
blc.bus_line_desc = 'COMMERCIAL'
AND
unet.fst_srvc_dt between &startdt and &enddt
and
unet.prov_prtcp_sts_cd in ('N') /***added***/
and
unet.chrg_sts_cd in ('P')
and
unet.alloc_rvnu_cd not in ('0450','0451','0452','0453','0454','0455','0456','0457','0458','0459'))b
on a.clm_aud_nbr=b.clm_aud_nbr
group by
a.clm_aud_nbr,
mbr_sys_id,
fst_srvc_dt,
adj_hccc_cd,
prov_tin,
proc_cd,
compat_cd,
alloc_rvnu_cd,
mpin,
full_nm,
finc_arng_cd,
finc_arng_desc,
ama_pl_of_srvc_cd,
ama_pl_of_srvc_desc,
member_mkt,
Provider_mkt,
Prov_Status, /***added just in to QC***/
FACL_TYP_TXT)
order by clm_aud_nbr;
disconnect from db2;
quit;
