[Scott Best]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

[Scott Best]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

FraudReferralForm_LE_10.12.18.docx 49 KB Word Document - Click to view

Options

• Copy Download Link

[Scott Best]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

[Scott Best]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

[Scott Best]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

Counts_By_procedure_By_CY_STEP_1.sql_.txt 5 KB Text File - Click to view

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]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

Compare-Prescribers-Prescribing-of-GPI-Categories-GT-25-Above-Peers.sql_.txt 11 KB Text File - Click to view

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]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

Compare-Billers-use-of-Prcdr-Codes-with-others-same-taxonomy_New.sql_.txt 25 KB Text File - Click to view

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]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login

Compare-Providers-use-of-Prcdr-Codes-with-others-same-taxonomy_New.sql_.txt 17 KB Text File - Click to view

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]

This content is only available to State Program Integrity members only. If you believe you should have access please reach out to the site administrators at contact@nampi.net with your email, title, state, and state PI lead name.

Login