Timeline – Scott Best – NAMPI Member Portal

Scott Best replied to the discussion MFCU Questions in the forum State Only Forum 2 years ago

2 years ago

Reply to MFCU Questions

Here is our Managed Care Fraud Referral Form.

Scott Best replied to the discussion MFCU Questions in the forum State Only Forum 2 years ago

2 years ago

Reply to MFCU Questions

Here is our Fraud Referral Form.

Options

Copy Download Link

Scott Best replied to the discussion MFCU Questions in the forum State Only Forum 2 years ago

2 years ago

Reply to MFCU Questions

Washington PI does not meet with MFCU before we submit a referral. We send a form with any supporting information as soon as it is determined to be Fraud. We meet with our MFCU counterparts every month for a deconfliction meeting. When we refer a case to MFCU they are marked in our case management as being “with MFCU”. They are not closed but…

Scott Best replied to the discussion Request for Assistance – Bringing PI fraud analytics in-house in the forum State Only Forum 2 years ago

2 years ago

Reply to Request for Assistance - Bringing PI fraud analytics in-house

Here is a comparison of all procedure codes. this requires Counts_By_procedure_By_CY_STEP_1.sql to be run first.

Scott Best replied to the discussion Request for Assistance – Bringing PI fraud analytics in-house in the forum State Only Forum 2 years ago

2 years ago

Reply to Request for Assistance - Bringing PI fraud analytics in-house

Another analysis I think is really interesting is comparing procedure codes over time to see what is on the rise. The first step is to get counts of providers claims and cost by calendar year then you can do all kinds of analysis like which procedures are increasing and costing more.

Options

Copy Download Link

Drop Table Proc_ClaimsCountCostByCY;

Create Table Proc_ClaimsCountCostByCY as
select 
To_Char(h.from_srvc_date,'YYYY') as Calendar_Year,
Case when h.bsns_status_cid = 71 then
     'FFS'
     else    
     'Managed_Care'  end  as PLAN_TYPE,
Case when h.bsns_status_cid = 71 then
     'FFS'
     else    
     h.blng_prvdr_name  end  as PLAN_NAME,
h.bsns_status_cid,
Case when h.bsns_status_cid = 71 then
     h.blng_prvdr_lctn_idntfr 
     else    
     PT.PRVDR_LCTN_IDNTFR end as PRVDR_LCTN_IDNTFR,
Case when h.bsns_status_cid = 71 then
     h.blng_prvdr_last_name 
     else    
     PT.PRVDR_LAST_NAME end as PRVDR_LAST_NAME,
--h.clm_type_cid,
l.prcdr_code,
--h.clm_type_name,
c.prcdr_long_desc,
Count(distinct h.clm_header_h_sid) as ClaimCount,
la.amount_type_lkpcd,
la.amount_type_name,
Case when h.bsns_status_cid = 71 then
     Sum(l.paid_amt)
     else
     Sum(la.claim_amt_value)end as Cost
from p1dwapp.clm_header_h h
Inner Join p1dwapp.clm_line_s l on l.clm_header_h_sid = h.clm_header_h_sid and l.bsns_status_cid in('71','83') and l.prcdr_code is not null
Left Join p1dwapp.clm_ln_amount_s la on la.clm_line_s_sid = l.clm_line_s_sid and la.amount_type_lkpcd = '02'
Left Join p1dwapp.ref_procedure_detail_s c on l.prcdr_iid = c.prcdr_iid and c.current_flag = 'Y' and l.from_srvc_date between c.from_date and c.to_date
Left Join p1dwapp.clm_hdr_prvdr_lctn_l PT on h.clm_header_h_sid = PT.CLM_HEADER_H_SID and PT.CLM_PRVDR_TYPE_LKPCD in ('PT', 'MN')
--Left Join p1dwapp.clm_hdr_amount_s cha on h.clm_header_h_sid = cha.clm_header_h_sid and cha.amount_type_lkpcd = '02'
where h.bsns_status_cid in ('71', '83') and l.prcdr_code is not null
and h.from_srvc_date > TO_DATE('30/06/'||TO_CHAR(add_months(sysdate,-12*11),'YYYY'),'dd/mm/yyyy')  -- ''30Jun2015'
Group By
To_Char(h.from_srvc_date,'YYYY'),
Case when h.bsns_status_cid = 71 then
     'FFS'
     else    
     h.blng_prvdr_name  end,
h.bsns_status_cid,
Case when h.bsns_status_cid = 71 then
     h.blng_prvdr_lctn_idntfr 
     else    
     PT.PRVDR_LCTN_IDNTFR end,
Case when h.bsns_status_cid = 71 then
     h.blng_prvdr_last_name 
     else    
     PT.PRVDR_LAST_NAME end,
--h.clm_type_cid,
l.prcdr_code,
--h.clm_type_name,
c.prcdr_long_desc,
la.amount_type_lkpcd,
la.amount_type_name;

-- tab=All_cnts_by_Clm_Type_By_CY
declare
myvar clob; pivotclause clob; nvar number;
myvar2 clob; pivotclause2 clob; nvar2 number;
myvar3 clob; pivotclause3 clob; nvar3 number;
begin

FOR nvar IN REVERSE 0..6
    LOOP
       myvar := myvar||to_Char(add_months(sysdate,-12 * nvar),'YYYY')||' as YEAR_'||to_Char(add_months(sysdate,-12 * nvar),'YYYY')||'_PROVIDERS,';
    END LOOP;
  myvar := substr(myvar, 0, length(myvar) -1);
    FOR nvar2 IN REVERSE 0..6
    LOOP
       myvar2 := myvar2||to_Char(add_months(sysdate,-12 * nvar2),'YYYY')||' as YEAR_'||to_Char(add_months(sysdate,-12 * nvar2),'YYYY')||'_CLAIMS,';
    END LOOP;
  myvar2 := substr(myvar2, 0, length(myvar2) -1);
  FOR nvar3 IN REVERSE 0..6
    LOOP
       myvar3 := myvar3||to_Char(add_months(sysdate,-12 * nvar3),'YYYY')||' as YEAR_'||to_Char(add_months(sysdate,-12 * nvar3),'YYYY')||'_COST,';
    END LOOP;
  myvar3 := substr(myvar3, 0, length(myvar3) -1);

EXECUTE IMMEDIATE 'Drop Table ALL_PROC_PROVIDERS_BY_YEAR';

pivotclause :=  'Create Table ALL_PROC_PROVIDERS_BY_YEAR as Select * from 
(Select T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc, Count(distinct T16A.prvdr_lctn_idntfr) as PROVIDERS from Proc_ClaimsCountCostByCY T16A where (T16A.amount_type_name is null or T16A.amount_type_lkpcd = ''02'') Group By T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc) i
PIVOT (SUM(i.PROVIDERS)
For Calendar_Year 
in ('||myvar||')
)';

EXECUTE IMMEDIATE 'Drop Table ALL_PROC_CLAIMS_BY_YEAR';

pivotclause2 :=  'Create Table ALL_PROC_CLAIMS_BY_YEAR as Select * from 
(Select T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc, sum(T16A.claimcount) as All_Count from Proc_ClaimsCountCostByCY T16A where (T16A.amount_type_name is null or T16A.amount_type_lkpcd = ''02'') Group By T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc) i
PIVOT (SUM(i.All_Count)
For Calendar_Year 
in ('||myvar2||')
)';

EXECUTE IMMEDIATE 'Drop Table ALL_PROC_COST_BY_YEAR';

pivotclause3 :=  'Create Table ALL_PROC_COST_BY_YEAR as Select * from 
(Select T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc, sum(T16A.cost) as All_Cost from Proc_ClaimsCountCostByCY T16A where (T16A.amount_type_name is null or T16A.amount_type_lkpcd = ''02'') Group By T16A.Calendar_Year, T16A.prcdr_code, T16A.prcdr_long_desc) i
PIVOT (SUM(i.All_Cost)
For Calendar_Year 
in ('||myvar3||')
)';
  dbms_output.put_line(pivotclause); 
  execute immediate pivotclause;
  execute immediate pivotclause2;
  execute immediate pivotclause3;
end;

Expand

Scott Best replied to the discussion Request for Assistance – Bringing PI fraud analytics in-house in the forum State Only Forum 2 years ago

2 years ago

Reply to Request for Assistance - Bringing PI fraud analytics in-house

Compare prescribers.

Options

Copy Download Link

Drop Table Counts_RX_ALL_PER_TXNMY_PRVDR;
Create Table Counts_RX_ALL_PER_TXNMY_PRVDR as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
max(cptp.srvcng_prvdr_lctn_txnmy_code) as srvcng_prvdr_lctn_txnmy_code,
Max(cptp.claims) as PRCDR_CLAIMS,
Max(cptp.clients) as PRCDR_CLIENTS,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
hp.prscrbr_id,
Count(distinct hp.srvc_prvdr_name) as DISTINCT_PHARMACIES_ALL,
Count(distinct h.clm_header_h_sid) as CLAIMS,
Count(distinct h.mbr_h_sid) as Clients,
Sum(lp.sbmtd_dispensed_quantity) as TOTAL_QTY,
/*Case when h.bsns_status_cid = 71 then
  Sum(h.paid_amt) 
  end as FFS_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Case When h.clm_type_cid = '24' then
         Sum(h.Total_Billed_Amt)
         End
  end as MC_HEADER_RX_PAID,*/
CASE
  WHEN h.bsns_status_cid = 71 THEN h.paid_amt
  ELSE lp.sbmtd_usual_customary_amt
END as Cost
from P1DWAPP.CLM_LN_PHRMCY_DTL_S lp
Inner join p1dwapp.clm_line_s l on l.clm_line_s_sid = lp.clm_line_s_sid and l.bsns_status_cid in('71','83')
Inner Join p1dwapp.clm_header_h h on l.clm_header_h_sid = h.clm_header_h_sid and h.bsns_status_cid in('71','83')
Inner Join p1dwapp.clm_hdr_phrmcy_dtl_s hp on hp.clm_header_h_sid = l.clm_header_h_sid
Inner Join Counts_PER_TXNMY_PRVDR cptp on To_Char(cptp.srvcng_national_prvdr_idntfr) = hp.prscrbr_id and cptp.year = To_Char(h.from_srvc_date, 'YYYY')
and cptp.claims = (Select max(c.claims) from Counts_PER_TXNMY_PRVDR c where To_Char(c.srvcng_national_prvdr_idntfr) = hp.prscrbr_id and c.year = To_Char(h.from_srvc_date, 'YYYY'))
Where h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') --and rownum  10000
;

Drop Table Counts_RX_PER_TXNMY_PRVDR_GPI;
Create Table Counts_RX_PER_TXNMY_PRVDR_GPI as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
max(cptp.srvcng_prvdr_lctn_txnmy_code) as srvcng_prvdr_lctn_txnmy_code,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
hp.prscrbr_id,
Substr(lp.Gpi_Nmbr,0,2) as GPI_CATEGORY,
lp.drg_administration_route,
Count(distinct hp.srvc_prvdr_name) as DISTINCT_PHARMACIES_GPI,
Count(distinct h.clm_header_h_sid) as CLAIMS,
Count(distinct h.mbr_h_sid) as Clients,
Sum(lp.sbmtd_dispensed_quantity) as TOTAL_QTY,
/*Case when h.bsns_status_cid = 71 then
  Sum(h.paid_amt) 
  end as FFS_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Case When h.clm_type_cid = '24' then
         Sum(h.Total_Billed_Amt)
         End
  end as MC_HEADER_RX_PAID,*/
CASE
  WHEN h.bsns_status_cid = 71 THEN h.paid_amt
  ELSE lp.sbmtd_usual_customary_amt
END as Cost
from P1DWAPP.CLM_LN_PHRMCY_DTL_S lp
Inner join p1dwapp.clm_line_s l on l.clm_line_s_sid = lp.clm_line_s_sid and l.bsns_status_cid in('71','83')
Inner Join p1dwapp.clm_header_h h on l.clm_header_h_sid = h.clm_header_h_sid and h.bsns_status_cid in('71','83')
Inner Join p1dwapp.clm_hdr_phrmcy_dtl_s hp on hp.clm_header_h_sid = l.clm_header_h_sid
Inner Join Counts_PER_TXNMY_PRVDR cptp on To_Char(cptp.srvcng_national_prvdr_idntfr) = hp.prscrbr_id and cptp.year = To_Char(h.from_srvc_date, 'YYYY')
and cptp.claims = (Select max(c.claims) from Counts_PER_TXNMY_PRVDR c where To_Char(c.srvcng_national_prvdr_idntfr) = hp.prscrbr_id and c.year = To_Char(h.from_srvc_date, 'YYYY'))
Where h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR')--and rownum  10000
;

Drop Table Counts_RX_PER_TXNMY;
Create Table Counts_RX_PER_TXNMY as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
cptp.srvcng_prvdr_lctn_txnmy_code,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
--Substr(lp.Gpi_Nmbr,0,2) as GPI_CATEGORY,
Count(distinct h.clm_header_h_sid) as CLAIMS,
Count(distinct h.mbr_h_sid) as Clients,
Sum(lp.sbmtd_dispensed_quantity) as TOTAL_QTY,
/*Case when h.bsns_status_cid = 71 then
  Sum(h.paid_amt) 
  end as FFS_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Case When h.clm_type_cid = '24' then
         Sum(h.Total_Billed_Amt)
         End
  end as MC_HEADER_RX_PAID,*/
Case when h.bsns_status_cid = '71' then
  Sum(h.paid_amt) 
  else
  Sum(h.Total_Billed_Amt)
  end as COST
from P1DWAPP.CLM_LN_PHRMCY_DTL_S lp
inner join p1dwapp.clm_line_s l on l.clm_line_s_sid = lp.clm_line_s_sid and l.bsns_status_cid in('71','83')
inner Join p1dwapp.clm_header_h h on l.clm_header_h_sid = h.clm_header_h_sid and h.bsns_status_cid in('71','83')
inner Join p1dwapp.clm_hdr_phrmcy_dtl_s hp on hp.clm_header_h_sid = l.clm_header_h_sid
Left Join Counts_PER_TXNMY_PRVDR cptp on To_Char(cptp.srvcng_national_prvdr_idntfr) = hp.prscrbr_id and cptp.year = To_Char(h.from_srvc_date, 'YYYY')
Where h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') --and rownum  TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') --and rownum  0 and crptg.clients > 0 and crptg.total_qty > 0 and crptg.cost > 0
Left Join gpi_descriptions gd on crptg.gpi_category = gd.trimmedgpi
--Left Join Counts_PER_TXNMY_PRVDR cptp on To_Char(cptp.srvcng_national_prvdr_idntfr) = crptg.prscrbr_id and cptp.year = crptg.year and cptp.bsns_status_cid = cra.bsns_status_cid and cptp.srvcng_prvdr_lctn_txnmy_code = cra.srvcng_prvdr_lctn_txnmy_code
Where crptg.cost > 0 and crptg.total_qty > 0 and crptg.claims > 0 and crptg.clients > 0
Group by
crptg.year,
crptg.srvcng_prvdr_lctn_txnmy_code,
crptg.bsns_status_cid,
crptg.gpi_category,
crptg.drg_administration_route,
gd.gpi_name,
crptg.claims/crpt.claims,
crptg.clients/crpt.clients,
crptg.total_qty/crpt.total_qty,
crptg.cost/crpt.cost
;

Drop Table RX_SCRIPTS_25_PCT_ABOVE_PEERS;
Create Table RX_SCRIPTS_25_PCT_ABOVE_PEERS as
Select
cra.year,
cra.srvcng_prvdr_lctn_txnmy_code,
cra.PRCDR_CLAIMS,
cra.PRCDR_CLIENTS,
td.txnmy_name,
td.txnmy_desc,
cra.bsns_status_cid,
cra.clm_type_cid,
cra.clm_type_name,
cra.prscrbr_id,
cptp.srvcng_prvdr_lctn_idntfr, 
cptp.srvcng_prvdr_last_name,
crp.gpi_category,
gd.gpi_name,
crp.drg_administration_route,
cra.DISTINCT_PHARMACIES_ALL,
crp.DISTINCT_PHARMACIES_GPI,
crp.claims as GPI_CAT_Claims,
cra.claims as Total_Claims,
crp.claims/cra.claims as PCT_OF_CLAIMS,
apg.avg_pct_of_claims,
crp.clients as GPI_CAT_CLIENTS,
cra.clients as TOTAL_CLIENTS,
crp.clients/cra.clients as PCT_OF_CLIENTS,
apg.avg_pct_of_clients,
crp.total_qty as GPI_CAT_QTY,
cra.total_qty as TOTAL_QTY,
crp.total_qty/cra.total_qty as PCT_OF_QTY,
apg.avg_pct_of_qty,
crp.cost as GPI_CAT_COST,
cra.cost as TOTAL_COST,
crp.cost/cra.cost as PCT_OF_COST,
apg.avg_pct_of_cost
from counts_rx_all_per_txnmy_prvdr cra
Inner Join counts_rx_per_txnmy_prvdr_gpi crp on crp.prscrbr_id = cra.prscrbr_id and crp.year = cra.year and crp.bsns_status_cid = cra.bsns_status_cid and crp.claims > 0 and crp.clients > 0 and crp.total_qty > 0 and crp.cost > 0
Left Join RX_CLAIMS_AVG_PCT_BY_GPI apg on apg.bsns_status_cid = crp.bsns_status_cid and apg.drg_administration_route = crp.drg_administration_route and apg.gpi_category = crp.gpi_cat...

Expand

This file was truncated for preview. Please download to view the full file.

Scott Best replied to the discussion Request for Assistance – Bringing PI fraud analytics in-house in the forum State Only Forum 2 years ago

2 years ago

Reply to Request for Assistance - Bringing PI fraud analytics in-house

Billers use of Procedure codes

Options

Copy Download Link

Drop Table Counts_PER_TXNMY_BILLER;
Create Table Counts_PER_TXNMY_BILLER as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
Substr(NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),1,10) as blng_prvdr_lctn_txnmy_code,
--substr(NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),1,10) as SRVCNG_PRVDR_LCTN_TXNMY_CODE,
NVL(hpl.prvdr_lctn_idntfr,h.blng_national_prvdr_idntfr) as BLNG_NATIONAL_PRVDR_IDNTFR,
--NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr) as SRVCNG_PRVDR_LCTN_IDNTFR,
Max(NVL(hpl.prvdr_last_name,h.blng_prvdr_last_name)) as BLNG_PRVDR_LAST_NAME,
Count(distinct NVL(hpl.prvdr_lctn_h_sid,h.blng_prvdr_lctn_h_sid)) as PROVIDERS,
Count(distinct NVL(l.srvcng_prvdr_lctn_h_sid,h.srvcng_prvdr_lctn_h_sid)) as SRVCNG_PROVIDERS,
Count(distinct Case when h.patient_gender_lkpcd = 'M' then h.mbr_idntfr else '' end) as Male_Clients,
Count(distinct Case when h.patient_gender_lkpcd = 'F' then h.mbr_idntfr else '' end) as Female_Clients,
Count(distinct h.mbr_h_sid) as CLIENTS,
Min(h.mbr_age) as MIN_CLNT_AGE,
Max(h.mbr_age) as MAX_CLNT_AGE,
Avg(h.mbr_age) as AVG_CLNT_AGE,
Count(distinct l.clm_header_h_sid) as CLAIMS,
Count(distinct l.clm_line_s_sid) as LINES,
Sum(l.paid_srvc_units) as TOTAL_UNITS,
Case when h.bsns_status_cid = 71 then
  Sum(h.paid_amt) 
  end as FFS_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Sum(ha.claim_amt_value)
  end as MC_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Case When h.clm_type_cid = '24' then
         Sum(h.Total_Billed_Amt)
         End
  end as MC_HEADER_RX_PAID,
Case when l.bsns_status_cid = 71 then
  Sum(l.paid_amt) 
  end as FFS_LINE_PAID,
Case when l.bsns_status_cid = 83 then
  Sum(la.claim_amt_value)
  end as MC_LINE_PAID,
Sum(NVL(NVL(la.claim_amt_value,l.paid_amt),0)) as TOTAL_PAID_AT_LINE
from P1DWAPP.CLM_LINE_S l
Inner Join p1dwapp.clm_header_h h on h.clm_header_h_sid = l.clm_header_h_sid and h.bsns_status_cid in('71','83')
Left Join p1dwapp.clm_hdr_prvdr_lctn_l hpl on hpl.clm_header_h_sid = l.clm_header_h_sid and hpl.clm_prvdr_type_lkpcd = 'MN'
Left Join p1dwapp.clm_hdr_amount_s ha on ha.clm_header_h_sid = h.clm_header_h_sid and ha.amount_type_lkpcd = '02'
Left Join p1dwapp.clm_ln_amount_s la on la.clm_line_s_sid = l.clm_line_s_sid and la.amount_type_lkpcd = '02'
Left join p1dwapp.ref_procedure_detail_s pd on pd.prcdr_iid = l.prcdr_iid and pd.current_flag = 'Y' and sysdate between pd.from_date and pd.to_date
where l.bsns_status_cid in('71','83') and h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') -- and TRUNC(sysdate), 'YEAR') -1
and NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) is not null and l.prcdr_code is not null and NVL(la.claim_amt_value,l.paid_amt) is not null
Group By
To_Char(h.from_srvc_date, 'YYYY'),
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
h.bsns_status_cid,
l.bsns_status_cid,
SubStr(NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),1,10),
--SubStr(NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),1,10),
NVL(hpl.prvdr_lctn_idntfr,h.blng_national_prvdr_idntfr)--,
--NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr),
--NVL(hpl.prvdr_last_name,h.blng_prvdr_last_name)
;

Drop Table Counts_TXNMY_PRCDR_BILLER_MDFR;
Create Table Counts_TXNMY_PRCDR_BILLER_MDFR as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code) as blng_prvdr_lctn_txnmy_code,
--NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) as SRVCNG_PRVDR_LCTN_TXNMY_CODE,
NVL(hpl.prvdr_lctn_idntfr,h.blng_national_prvdr_idntfr) as BLNG_NATIONAL_PRVDR_IDNTFR,
--NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr) as SRVCNG_PRVDR_LCTN_IDNTFR,
Max(NVL(hpl.prvdr_last_name,h.blng_prvdr_last_name)) as BLNG_PRVDR_LAST_NAME,
l.prcdr_code,
pd.prcdr_long_desc,
--l.mdfr_code1,
--l.mdfr_code2,
--l.primary_pa_crtfctn_idntfr,
Count(distinct NVL(hpl.prvdr_lctn_h_sid,h.blng_prvdr_lctn_h_sid)) as PROVIDERS,
--Count(distinct NVL(l.srvcng_prvdr_lctn_h_sid,h.srvcng_prvdr_lctn_h_sid)) as SRVCNG_PROVIDERS,
Min(h.mbr_age) as MIN_CLNT_AGE,
Max(h.mbr_age) as MAX_CLNT_AGE,
Avg(h.mbr_age) as AVG_CLNT_AGE,
Count(distinct Case when h.patient_gender_lkpcd = 'M' then h.mbr_idntfr else '' end) as Male_Clients,
Count(distinct Case when h.patient_gender_lkpcd = 'F' then h.mbr_idntfr else '' end) as Female_Clients,
Count(distinct h.mbr_h_sid) as CLIENTS,
Count(distinct l.clm_header_h_sid) as CLAIMS,
Sum(l.paid_srvc_units) as TOTAL_UNITS,
Sum(NVL(NVL(la.claim_amt_value,l.paid_amt),0)) as TOTAL_PAID_AT_LINE
from P1DWAPP.CLM_LINE_S l
Inner Join p1dwapp.clm_header_h h on h.clm_header_h_sid = l.clm_header_h_sid
Left Join p1dwapp.clm_hdr_prvdr_lctn_l hpl on hpl.clm_header_h_sid = l.clm_header_h_sid and hpl.clm_prvdr_type_lkpcd = 'MN'
Left Join p1dwapp.clm_ln_amount_s la on la.clm_line_s_sid = l.clm_line_s_sid and la.amount_type_lkpcd = '02'
inner join p1dwapp.ref_procedure_detail_s pd on pd.prcdr_iid = l.prcdr_iid and pd.current_flag = 'Y' and sysdate between pd.from_date and pd.to_date
where l.bsns_status_cid in('71','83') and h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') -- and TRUNC(sysdate), 'YEAR') -1
and NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) is not null and l.prcdr_code is not null and NVL(la.claim_amt_value,l.paid_amt) is not null
Group By
To_Char(h.from_srvc_date, 'YYYY'),
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),
--NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),
NVL(hpl.prvdr_lctn_idntfr,h.blng_national_prvdr_idntfr),
--NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr),
--NVL(hpl.prvdr_last_name,h.blng_prvdr_last_name),
pd.prcdr_long_desc,
l.prcdr_code--,
--l.mdfr_code1,
--l.mdfr_code2,
--l.primary_pa_crtfctn_idntfr
;

Drop Table counts_txnmy_prcdr_billr_CMP2;
Create Table counts_txnmy_prcdr_billr_CMP2 as
select
cptp.year,
cptp.bsns_status_cid,
cptp.clm_type_cid,
cptp.clm_type_name,
cptpp.virtual_clm_type_name,
cptp.blng_prvdr_lctn_txnmy_code,
--cptp.srvcng_prvdr_lctn_txnmy_code,
cptp.blng_national_prvdr_idntfr,
--cptp.srvcng_prvdr_lctn_idntfr,
Max(cptp.blng_prvdr_last_name) blng_prvdr_last_name,
cptpp.prcdr_code, 
cptpp.prcdr_long_desc,
--cptpp.mdfr_code1,
--cptpp.mdfr_code2,
--cptpp.primary_pa_crtfctn_idntfr,
cptpp.male_clients as PRCDR_MALE_CLNTS,
cptpp.female_clients as PRCDR_FEMALE_CLNTS,
cptp.male_clients as ALL_MALE_CLNTS,
cptp.female_clients as ALL_FEMALE_CLNTS,
cptpp.MIN_CLNT_AGE as PRCDR_CLNT_MIN_AGE,
cptpp.MAX_CLNT_AGE as PRCDR_CLNT_MAX_AGE,
cptp.MIN_CLNT_AGE as ALL_CLNT_MIN_AGE,
cptp.MAX_CLNT_AGE as ALL_CLNT_MAX_AGE,
cptpp.AVG_CLNT_AGE as PRCDR_CLNT_AVG_AGE,
cptp.AVG_CLNT_AGE as ALL_CLNT_AVG_AGE,
cptpp.clients as PRCDR_CLIENTS,
cptp.clients as TOTAL_CLIENTS,
cptpp.clients/cptp.clients as PERCENT_OF_CLIENTS,
cptpp.claims as PRCDR_CLAIMS,
cptp.claims as TOTAL_CLAIMS,
cptpp.claims/cptp.claims as PERCENT_OF_CLAIMS,
cptpp.total_units as PRCDR_UNITS,
cptp.total_units as TOTAL_UNITS,
cptpp.total_units/cptp.total_units as PERCENT_OF_UNITS,
cptpp.total_paid_at_line as PRCDR_PAID_AT_LINE,
cptp.total_paid_at_line as TOTAL_PAID_AT_LINE,
cptpp.total_paid_at_line/cptp.total_paid_at_line as PERCENT_OF_PAID_AT_LINE
from COUNTS_PER_TXNMY_BILLER cptp
inner join Counts_TXNMY_PRCDR_BILLER_MDFR cptpp on cptpp.blng_national_prvdr_idntfr = cptp.blng_national_prvdr_idntfr and 
cptpp.year = cptp.year and cptpp.bsns_status_cid = cptp.bsns_status_cid and cptpp.clm_type_cid = cptp.clm_type_cid and 
cptpp.blng_prvdr_lctn_txnmy_code = cptp.blng_prvdr_lctn_txnmy_code --and --cptpp.srvcng_prvdr_lctn_txnmy_code = cptp.srvcng_prvdr_lctn_txnmy_code and
--cptpp.blng_prvdr_last_name = cptp.blng_prvdr_last_name and cptpp.blng_national_prvdr_idntfr = cptp.blng_national_prvdr_idntfr
Where cptpp.clients > 0 and cptpp.claims > 0 and cptpp.total_units > 0 and cptpp.total_paid_at_line > 0 and
cptp.clients > 0 and cptp.claims > 0 and cptp.total_units > 0 and cptp.total_paid_at_line > 0
Group by
cptp.year,
cptp.bsns_status_cid,
cptp.clm_type_cid,
cptp.clm_type_name,
cptpp.virtual_clm_type_name,
cptp.blng_prvdr_lctn_txnmy_code,
--cptp.srvcng_prvdr_lctn_txnmy_code,
cptp.blng_national_prvdr_idntfr,
--cptp.srvcng_prvdr_lctn_idntfr,
cptpp.prcdr_code, 
cptpp.prcdr_long_desc,
--cptpp.mdfr_code1,
--cptpp.mdfr_code2,
--cptpp.primary_pa_crtfctn_idntfr,
cptpp.male_clients,
cptpp.female_clients,
cptp.male_clients,
cptp.female_clients,
cptpp.MIN_CLNT_AGE,
cptpp.MAX_CLNT_AGE,
cptp.MIN_CLNT_AGE,
cptp.MAX_CLNT_AGE,
cptpp.AVG_CLNT_AGE,
cptp.AVG_CLNT_AGE,
cptpp.clients,
cptp.clients,
cptpp.clients/cptp.clients,
cptpp.claims,
cptp.claims,
cptpp.claims/cptp.claims,
cptpp.total_units,
cptp.total_units,
cptpp.total_units/cptp.total_units,
cptpp.total_paid_at_line,
cptp.total_paid_at_line,
cptpp.total_paid_at_line/cptp.total_paid_at_line
;

Drop Table counts_txnmy_prcdr_billr_CMP3;
Create Table counts_txnmy_prcdr_billr_CMP3 as
select
cptp.year,
cptp.bsns_status_cid,
cptp.clm_type_cid,
cptp.clm_type_name,
cptpp.virtual_clm_type_name,
cptp.blng_prvdr_lctn_txnmy_code,
--cptp.srvcng_prvdr_lctn_txnmy_code,
--cptp.blng_national_prvdr_idntfr,
Count(distinct cptp.blng_national_prvdr_idntfr) as COUNT_OF_BLNG_PRVDRS,
--cptp.blng_prvdr_last_name,
cptpp.prcdr_code, 
cptpp.prcdr_long_desc,
--cptpp.mdfr_code1,
--cptpp.mdfr_code2,
--cptpp.primary_pa_crtfctn_idntfr,
Sum(cptpp.clients) as PRCDR_CLIENTS,
Sum(cptp.clients) as TOTAL_CLIENTS,
Sum(cptpp.clients)/Sum(cptp.clients) as AVG_PCT_OF_CLIENTS,
Sum(cptpp.claims) as PRCDR_CLAIMS,
Sum(cptp.claims) as TOTAL_CLAIMS,
Sum(cptpp.claims)/Sum(cptp.claims) as AVG_...

Expand

This file was truncated for preview. Please download to view the full file.

Scott Best replied to the discussion Request for Assistance – Bringing PI fraud analytics in-house in the forum State Only Forum 2 years ago

2 years ago

Reply to Request for Assistance - Bringing PI fraud analytics in-house

We have been without a Fraud and Abuse Detection System for quite some time. During that time, I developed a few really handy analyses. My favorite analysis compared servicing Providers, billing Providers, or prescribing providers use of procedure codes against all providers with the same taxonomy and reports those by year that are billing…

Options

Copy Download Link

Drop Table Counts_PER_TXNMY_PRVDR;
Create Table Counts_PER_TXNMY_PRVDR as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
Substr(NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),1,10) as blng_prvdr_lctn_txnmy_code,
substr(NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),1,10) as SRVCNG_PRVDR_LCTN_TXNMY_CODE,
NVL(l.srvcng_national_prvdr_idntfr,h.srvcng_national_prvdr_idntfr) as SRVCNG_NATIONAL_PRVDR_IDNTFR,
NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr) as SRVCNG_PRVDR_LCTN_IDNTFR,
NVL(l.srvcng_prvdr_last_name,h.srvcng_prvdr_last_name) as SRVCNG_PRVDR_LAST_NAME,
Count(distinct NVL(hpl.prvdr_lctn_h_sid,h.blng_prvdr_lctn_h_sid)) as PROVIDERS,
Count(distinct NVL(l.srvcng_prvdr_lctn_h_sid,h.srvcng_prvdr_lctn_h_sid)) as SRVCNG_PROVIDERS,
Count(distinct Case when h.patient_gender_lkpcd = 'M' then h.mbr_idntfr else '' end) as Male_Clients,
Count(distinct Case when h.patient_gender_lkpcd = 'F' then h.mbr_idntfr else '' end) as Female_Clients,
Count(distinct h.mbr_h_sid) as CLIENTS,
Min(h.mbr_age) as MIN_CLNT_AGE,
Max(h.mbr_age) as MAX_CLNT_AGE,
Avg(h.mbr_age) as AVG_CLNT_AGE,
Count(distinct l.clm_header_h_sid) as CLAIMS,
Count(distinct l.clm_line_s_sid) as LINES,
Sum(l.paid_srvc_units) as TOTAL_UNITS,
Case when h.bsns_status_cid = 71 then
  Sum(h.paid_amt) 
  end as FFS_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Sum(ha.claim_amt_value)
  end as MC_HEADER_PAID,
Case when h.bsns_status_cid = 83 then
  Case When h.clm_type_cid = '24' then
         Sum(h.Total_Billed_Amt)
         End
  end as MC_HEADER_RX_PAID,
Case when l.bsns_status_cid = 71 then
  Sum(l.paid_amt) 
  end as FFS_LINE_PAID,
Case when l.bsns_status_cid = 83 then
  Sum(la.claim_amt_value)
  end as MC_LINE_PAID,
Sum(NVL(NVL(la.claim_amt_value,l.paid_amt),0)) as TOTAL_PAID_AT_LINE
from P1DWAPP.CLM_LINE_S l
Inner Join p1dwapp.clm_header_h h on h.clm_header_h_sid = l.clm_header_h_sid and h.bsns_status_cid in('71','83')
Left Join p1dwapp.clm_hdr_prvdr_lctn_l hpl on hpl.clm_header_h_sid = l.clm_header_h_sid and hpl.clm_prvdr_type_lkpcd = 'MN'
Left Join p1dwapp.clm_hdr_amount_s ha on ha.clm_header_h_sid = h.clm_header_h_sid and ha.amount_type_lkpcd = '02'
Left Join p1dwapp.clm_ln_amount_s la on la.clm_line_s_sid = l.clm_line_s_sid and la.amount_type_lkpcd = '02'
Left join p1dwapp.ref_procedure_detail_s pd on pd.prcdr_iid = l.prcdr_iid and pd.current_flag = 'Y' and sysdate between pd.from_date and pd.to_date
where l.bsns_status_cid in('71','83') and h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') -- and TRUNC(sysdate), 'YEAR') -1
and NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) is not null and l.prcdr_code is not null and NVL(la.claim_amt_value,l.paid_amt) is not null
Group By
To_Char(h.from_srvc_date, 'YYYY'),
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
h.bsns_status_cid,
l.bsns_status_cid,
SubStr(NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),1,10),
SubStr(NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),1,10),
NVL(l.srvcng_national_prvdr_idntfr,h.srvcng_national_prvdr_idntfr),
NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr),
NVL(l.srvcng_prvdr_last_name,h.srvcng_prvdr_last_name)
;

Drop Table Counts_TXNMY_PRCDR_PRVDR_MDFR;
Create Table Counts_TXNMY_PRCDR_PRVDR_MDFR as
select
To_Char(h.from_srvc_date, 'YYYY') as YEAR,
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code) as blng_prvdr_lctn_txnmy_code,
NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) as SRVCNG_PRVDR_LCTN_TXNMY_CODE,
NVL(l.srvcng_national_prvdr_idntfr,h.srvcng_national_prvdr_idntfr) as SRVCNG_NATIONAL_PRVDR_IDNTFR,
NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr) as SRVCNG_PRVDR_LCTN_IDNTFR,
NVL(l.srvcng_prvdr_last_name,h.srvcng_prvdr_last_name) as SRVCNG_PRVDR_LAST_NAME,
l.prcdr_code,
pd.prcdr_long_desc,
l.mdfr_code1,
l.mdfr_code2,
l.primary_pa_crtfctn_idntfr,
Count(distinct NVL(hpl.prvdr_lctn_h_sid,h.blng_prvdr_lctn_h_sid)) as PROVIDERS,
Count(distinct NVL(l.srvcng_prvdr_lctn_h_sid,h.srvcng_prvdr_lctn_h_sid)) as SRVCNG_PROVIDERS,
Min(h.mbr_age) as MIN_CLNT_AGE,
Max(h.mbr_age) as MAX_CLNT_AGE,
Avg(h.mbr_age) as AVG_CLNT_AGE,
Count(distinct Case when h.patient_gender_lkpcd = 'M' then h.mbr_idntfr else '' end) as Male_Clients,
Count(distinct Case when h.patient_gender_lkpcd = 'F' then h.mbr_idntfr else '' end) as Female_Clients,
Count(distinct h.mbr_h_sid) as CLIENTS,
Count(distinct l.clm_header_h_sid) as CLAIMS,
Sum(l.paid_srvc_units) as TOTAL_UNITS,
Sum(NVL(NVL(la.claim_amt_value,l.paid_amt),0)) as TOTAL_PAID_AT_LINE
from P1DWAPP.CLM_LINE_S l
Inner Join p1dwapp.clm_header_h h on h.clm_header_h_sid = l.clm_header_h_sid
Left Join p1dwapp.clm_hdr_prvdr_lctn_l hpl on hpl.clm_header_h_sid = l.clm_header_h_sid and hpl.clm_prvdr_type_lkpcd = 'MN'
Left Join p1dwapp.clm_ln_amount_s la on la.clm_line_s_sid = l.clm_line_s_sid and la.amount_type_lkpcd = '02'
inner join p1dwapp.ref_procedure_detail_s pd on pd.prcdr_iid = l.prcdr_iid and pd.current_flag = 'Y' and sysdate between pd.from_date and pd.to_date
where l.bsns_status_cid in('71','83') and h.from_srvc_date > TRUNC(TO_DATE(add_months(sysdate,-12 * 4)), 'YEAR') -- and TRUNC(sysdate), 'YEAR') -1
and NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code) is not null and l.prcdr_code is not null and NVL(la.claim_amt_value,l.paid_amt) is not null
Group By
To_Char(h.from_srvc_date, 'YYYY'),
h.bsns_status_cid,
h.clm_type_cid, 
h.clm_type_name,
h.virtual_clm_type_name,
NVL(hpl.txnmy_code,h.blng_prvdr_lctn_txnmy_code),
NVL(l.srvcng_prvdr_lctn_txnmy_code,h.srvcng_prvdr_lctn_txnmy_code),
NVL(l.srvcng_national_prvdr_idntfr,h.srvcng_national_prvdr_idntfr),
NVL(l.Srvcng_Prvdr_Lctn_Idntfr,h.srvcng_prvdr_lctn_idntfr),
NVL(l.srvcng_prvdr_last_name,h.srvcng_prvdr_last_name),
pd.prcdr_long_desc,
l.prcdr_code,
l.mdfr_code1,
l.mdfr_code2,
l.primary_pa_crtfctn_idntfr
;

Drop Table counts_txnmy_prcdr_prvdr_CMP2;
Create Table counts_txnmy_prcdr_prvdr_CMP2 as
select
cptp.year,
cptp.bsns_status_cid,
cptp.clm_type_cid,
cptp.clm_type_name,
cptpp.virtual_clm_type_name,
cptp.blng_prvdr_lctn_txnmy_code,
cptp.srvcng_prvdr_lctn_txnmy_code,
cptp.srvcng_national_prvdr_idntfr,
cptp.srvcng_prvdr_lctn_idntfr,
cptp.srvcng_prvdr_last_name,
cptpp.prcdr_code, 
cptpp.prcdr_long_desc,
cptpp.mdfr_code1,
cptpp.mdfr_code2,
cptpp.primary_pa_crtfctn_idntfr,
cptpp.male_clients as PRCDR_MALE_CLNTS,
cptpp.female_clients as PRCDR_FEMALE_CLNTS,
cptp.male_clients as ALL_MALE_CLNTS,
cptp.female_clients as ALL_FEMALE_CLNTS,
cptpp.MIN_CLNT_AGE as PRCDR_CLNT_MIN_AGE,
cptpp.MAX_CLNT_AGE as PRCDR_CLNT_MAX_AGE,
cptp.MIN_CLNT_AGE as ALL_CLNT_MIN_AGE,
cptp.MAX_CLNT_AGE as ALL_CLNT_MAX_AGE,
cptpp.AVG_CLNT_AGE as PRCDR_CLNT_AVG_AGE,
cptp.AVG_CLNT_AGE as ALL_CLNT_AVG_AGE,
cptpp.clients as PRCDR_CLIENTS,
cptp.clients as TOTAL_CLIENTS,
cptpp.clients/cptp.clients as PERCENT_OF_CLIENTS,
cptpp.claims as PRCDR_CLAIMS,
cptp.claims as TOTAL_CLAIMS,
cptpp.claims/cptp.claims as PERCENT_OF_CLAIMS,
cptpp.total_units as PRCDR_UNITS,
cptp.total_units as TOTAL_UNITS,
cptpp.total_units/cptp.total_units as PERCENT_OF_UNITS,
cptpp.total_paid_at_line as PRCDR_PAID_AT_LINE,
cptp.total_paid_at_line as TOTAL_PAID_AT_LINE,
cptpp.total_paid_at_line/cptp.total_paid_at_line as PERCENT_OF_PAID_AT_LINE
from COUNTS_PER_TXNMY_PRVDR cptp
inner join Counts_TXNMY_PRCDR_PRVDR_MDFR cptpp on cptpp.srvcng_national_prvdr_idntfr = cptp.srvcng_national_prvdr_idntfr and 
cptpp.year = cptp.year and cptpp.bsns_status_cid = cptp.bsns_status_cid and cptpp.clm_type_cid = cptp.clm_type_cid and 
cptpp.blng_prvdr_lctn_txnmy_code = cptp.blng_prvdr_lctn_txnmy_code and cptpp.srvcng_prvdr_lctn_txnmy_code = cptp.srvcng_prvdr_lctn_txnmy_code and
cptpp.srvcng_prvdr_last_name = cptp.srvcng_prvdr_last_name and cptpp.srvcng_prvdr_lctn_idntfr = cptp.srvcng_prvdr_lctn_idntfr
Where cptpp.clients > 0 and cptpp.claims > 0 and cptpp.total_units > 0 and cptpp.total_paid_at_line > 0 and
cptp.clients > 0 and cptp.claims > 0 and cptp.total_units > 0 and cptp.total_paid_at_line > 0
;

Drop Table counts_txnmy_prcdr_prvdr_CMP3;
Create Table counts_txnmy_prcdr_prvdr_CMP3 as
select
cptp.year,
cptp.bsns_status_cid,
cptp.clm_type_cid,
cptp.clm_type_name,
cptpp.virtual_clm_type_name,
cptp.blng_prvdr_lctn_txnmy_code,
cptp.srvcng_prvdr_lctn_txnmy_code,
--cptp.srvcng_national_prvdr_idntfr,
Count(distinct cptp.srvcng_prvdr_lctn_idntfr) as COUNT_OF_SRVCNG_PRVDRS,
--cptp.srvcng_prvdr_last_name,
cptpp.prcdr_code, 
cptpp.prcdr_long_desc,
cptpp.mdfr_code1,
cptpp.mdfr_code2,
cptpp.primary_pa_crtfctn_idntfr,
Sum(cptpp.male_clients) as PRCDR_MALE_CLNTS,
Sum(cptpp.female_clients) as PRCDR_FEMALE_CLNTS,
Min(cptpp.MIN_CLNT_AGE) as PRCDR_CLNT_MIN_AGE,
Max(cptpp.MAX_CLNT_AGE) as PRCDR_CLNT_MAX_AGE,
Min(cptp.MIN_CLNT_AGE) as ALL_CLNT_MIN_AGE,
Max(cptp.MAX_CLNT_AGE) as ALL_CLNT_MAX_AGE,
AVG(cptpp.AVG_CLNT_AGE) as PRCDR_CLNT_AVG_AGE,
AVG(cptp.AVG_CLNT_AGE) as ALL_CLNT_AVG_AGE,
Sum(cptpp.clients) as PRCDR_CLIENTS,
Sum(cptp.clients) as TOTAL_CLIENTS,
Sum(cptpp.clients)/Sum(cptp.clients) as AVG_PCT_OF_CLIENTS,
Sum(cptpp.claims) as PRCDR_CLAIMS,
Sum(cptp.claims) as TOTAL_CLAIMS,
Sum(cptpp.claims)/Sum(cptp.claims) as AVG_PCT_OF_CLAIMS,
Sum(cptpp.total_units) as PRCDR_UNITS,
Sum(cptp.total_units) as TOTAL_UNITS,
Sum(cptpp.total_units)/Sum(cptp.total_units) as AVG_PCT_OF_UNITS,
Sum(cptpp.total_paid_at_line) as PRCDR_PAID_AT_LINE,
Sum(cptp.total_paid_at_line) as TOTAL_PAID_AT_LINE,
Sum(cptpp.total_paid_at_line)/Sum(cptp.total_paid_at_line) as AVG_PCT_OF_PAID_AT_LINE
from COUNTS_PER_TXNMY_PRVDR cptp
inner join Counts_TXNMY_PRCDR_PRVDR_MDFR cptpp on cptpp.srvcng_national_prvdr_idntfr = cptp.srvcng_national_prvdr_idntfr and 
cp...

Expand

This file was truncated for preview. Please download to view the full file.

Scott Best replied to the discussion States who have or are seeking an exception to the Medicaid RAC requirement in the forum State Only Forum 2 years ago

2 years ago

Reply to States who have or are seeking an exception to the Medicaid RAC requirement

WA currently has a waiver

Scott Best

Submit your content for the NAMPI Resource Library