--For Testing
DECLARE @StartOfMonth DATETIME, --For Testing
@Epic_Vendor_ID varchar(max) = 'ALL', --For Testing
@Specialty_Cap_ID varchar(max) = 'NONE', --For Testing
@RateCode varchar (max) = 'ALL' --For Testing
SET @StartOfMonth = '2023-08-01 00:00:00.000' --For Testing
--SET @Epic_Vendor_ID = '19092' -- --For Testing
SET @Specialty_Cap_ID = '22' -- --For Testing
IF OBJECT_ID('tempdb..#temp') is not null
drop table #temp
select distinct
[Payee_Entity] = IsNull(rtrim(ven.VENDOR_NAME),''),
[FEDID] = IsNull(convert(varchar(9),ven_tin.TAX_ID),''),
[Payee_NPI] = '',
[PCP_Name] = IsNull(rtrim(cs.PROV_NAME),''),
[PCP_NPI] = IsNull(convert(varchar(10),prov_npi.IDENTITY_ID),''),
[Cap_Date] = CONVERT(VARCHAR(10),CONVERT(DATETIME, dtx.EFFECTIVE_DATE),101),
[LineOFBusiness] = IsNull(rtrim(cdiv.DIVISION_NAME),''),
[Member] = IsNull(rtrim(pat.PAT_NAME),''),
[Member_ID] = IsNull(rtrim(cml.MEM_NUMBER),''),
[VMC_MRN] = IsNull(rtrim(vmc_mrn.IDENTITY_ID),''),
[RateCode] = IsNull(rtrim(Ratecode.RateCode),''),
[CapAmount] = dtx.PCP_AMOUNT,
[PayDate] = CONVERT(VARCHAR(10),CONVERT(DATETIME, ap_check.CHECK_DATE),101),
[CheckNbr] = cast(dtx.CHECK_ID as varchar),
[CapType] = case when IsNull(dtx.SPECIAL_TX_TYPE_C,'') = '2'
then IsNull(rtrim(special_cap.NAME),'')
else IsNull(rtrim(cap_status.NAME),'')
end,
[COVERAGE_ID] = '0000000000', --Placeholder for this field so the #temp doesn't consider this as varchar
dtx.TX_ID --Placeholder for this field so the #temp doesn't consider this as varchar
into #temp
from CLARITY.dbo.CAP_PAY dtx
left join CLARITY.dbo.AP_CHECK ap_check
on ap_check.CHECK_ID = dtx.CHECK_ID
left join CLARITY.dbo.CLARITY_VENDOR ven
on ven.VENDOR_ID = dtx.VENDOR_ID
left join CLARITY.dbo.MGRP mem_grp
on mem_grp.MGRP_ID = dtx.DIST_MGRP_ID
left join CLARITY.dbo.CLARITY_POS eaf
on eaf.POS_ID = dtx.LOCATION_ID
left join CLARITY.dbo.CLARITY_SER cs
on cs.PROV_ID = dtx.PCP_PROV_ID
left join CLARITY.dbo.IDENTITY_SER_ID prov_npi
on prov_npi.PROV_ID = dtx.PCP_PROV_ID
and prov_npi.IDENTITY_TYPE_ID = '60' -- 60 is NPI (IDENTITY_ID_TYPE)
left join CLARITY.dbo.ZC_CAP_TX_TYPE dtxt
on dtxt.TX_TYPE_C = dtx.TX_TYPE_C
left join CLARITY.dbo.ZC_SPEC_TX_TYPE special_cap
on special_cap.SPECIAL_TX_TYPE_C = dtx.SPECIAL_TX_TYPE_C
left join CLARITY.dbo.ZC_CAP_TX_STATUS cap_status
on cap_status.TX_STATUS_C = dtx.TX_STATUS_C
--- 7/14/2023 CV added ---------------------------------
left join CLARITY.dbo.ZC_SPEC_TX_TYPE zdtx
on zdtx.SPECIAL_TX_TYPE_C = dtx.SPECIAL_TX_TYPE_C
left join CLARITY.dbo.CAP_TX_CNCT_INFO dtx20
on dtx20.TX_ID = dtx.TX_ID
left join CLARITY.dbo.COVERAGE_MEMBER_LIST cml
on cml.COVERAGE_ID = dtx.COVERAGE_ID
and cml.PAT_ID = dtx.MEM_ID
and (dtx.EFFECTIVE_DATE between cml.MEM_EFF_FROM_DATE and ISNULL(cml.MEM_EFF_TO_DATE, '12/31/2099') -- CV added open parenthesis
--or dtx20.CONTACT_DATE between cml.MEM_EFF_FROM_DATE and ISNULL(cml.MEM_EFF_TO_DATE, '12/31/2099')
) -- CV added
left join CLARITY.dbo.PATIENT pat
on pat.PAT_ID = dtx.MEM_ID
left join CLARITY.dbo.PLAN_GRP pg
on pg.PLAN_GRP_ID = dtx.PLAN_GRP_ID
left join CLARITY.dbo.CLARITY_DIVISION cdiv
on cdiv.DIVISION_ID = pg.DIV_ID
left join ( Select TX_CANC_TX_ID
From clarity.DBO.Cap_Pay
where TX_CANC_TX_ID is not Null
and EFFECTIVE_DATE = @StartOfMonth
) Cancel
on cancel.TX_CANC_TX_ID = dtx.Tx_ID
--- SCFHP RATECODE REGION
OUTER APPLY ( SELECT TOP 1 CVA.NAME as [RateCode], CMAT.EFF_DATE , CMAT.TERM_DATE
FROM clarity.DBO.COVERAGE_MEM_ATTR CMAT
LEFT JOIN clarity.DBO.ZC_CVG_ATTR CVA
ON CVA.CVG_ATTR_C = CMAT.CVG_ATTR_C
WHERE CMAT.CVG_ATTR_C BETWEEN 43 AND 54
AND CMAT.COVERAGE_ID = CML.COVERAGE_ID
AND pat.PAT_ID = CML.PAT_ID
AND CMAT.EFF_DATE BETWEEN cml.MEM_EFF_FROM_DATE AND ISNULL(cml.MEM_EFF_TO_DATE, '12/31/2099')
and dtx.EFFECTIVE_DATE BETWEEN CMAT.EFF_DATE AND ISNULL(CMAT.TERM_DATE, '12/31/2099')
--OR CMAT.EFF_DATE BETWEEN C.SERVICE_START_DATE AND C.SERVICE_END_DATE)
ORDER BY CMAT.TERM_DATE DESC
) Ratecode
---------Member VMC MRN---------
outer apply (select top 1 vmc_mrn.IDENTITY_ID
from CLARITY.dbo.IDENTITY_ID vmc_mrn
where vmc_mrn.PAT_ID = cml.PAT_ID
and vmc_mrn.IDENTITY_TYPE_ID = '165'
order by vmc_mrn.line desc) vmc_mrn
---------Provider Pay-To (Vendor) Tax ID---------
outer apply (select top 1 ven_tin.TAX_ID
from CLARITY.dbo.VENDOR_TAX_ID ven_tin
where ven_tin.VENDOR_ID = dtx.VENDOR_ID
and ven_tin.TAX_ID_EFF_DATE <= getdate()
order by ven_tin.TAX_ID_EFF_DATE desc) ven_tin
where @Specialty_Cap_ID = 'NONE'
and dtx.TX_TYPE_C= '2' -- PCP Capitation
and (dtx.CHECK_ID is not NULL
)
and (DATEPART(MM,ap_check.CHECK_DATE) = (DATEPART(MM,@StartOfMonth)))
and (DATEPART(YYYY,ap_check.CHECK_DATE) = (DATEPART(YYYY,@StartOfMonth)))
and (
(@Specialty_Cap_ID <> 'NONE' and dtx.DIST_MGRP_ID = @Specialty_Cap_ID)
or @Specialty_Cap_ID = 'NONE'
)
and (
(@Epic_Vendor_ID <> 'ALL' and dtx.VENDOR_ID = @Epic_Vendor_ID)
or @Epic_Vendor_ID = 'ALL'
)
and (
(@RateCode <> 'ALL' and RateCode.RateCode = @RateCode)
or @RateCode = 'ALL'
)
UNION
--Speciality Capitation
select distinct
[Payee_Entity] = IsNull(rtrim(ven.VENDOR_NAME),''),
[FEDID] = IsNull(convert(varchar(9),ven_tin.TAX_ID),''),
[Payee_NPI] = '',
[PCP_Name] = IsNull(rtrim(cs.PROV_NAME),''),
[PCP_NPI] = IsNull(convert(varchar(10),prov_npi.IDENTITY_ID),''),
[Cap_Date] = CONVERT(VARCHAR(10),CONVERT(DATETIME, dtx.EFFECTIVE_DATE),101),
[LineOFBusiness] = IsNull(rtrim(cdiv.DIVISION_NAME),''),
[Member] = IsNull(rtrim(pat.PAT_NAME),''),
[Member_ID] = IsNull(rtrim(cml.MEM_NUMBER),''),
[VMC_MRN] = IsNull(rtrim(vmc_mrn.IDENTITY_ID),''),
[RateCode] = IsNull(rtrim(Ratecode.RateCode),''),
[CapAmount] = specialty_cap_amount.SPEC_CAP_AMT,
[PayDate] = CONVERT(VARCHAR(10),CONVERT(DATETIME, ap_check.CHECK_DATE),101),
[CheckNbr] = cast(dtx.CHECK_ID as varchar),
[CapType] = case when IsNull(dtx.SPECIAL_TX_TYPE_C,'') = '2'
then IsNull(rtrim(special_cap.NAME),'')
else IsNull(rtrim(cap_status.NAME),'')
end,
[COVERAGE_ID] = cap_mem_list_detail.COVERAGE_LIST, --Placeholder for this field so the #temp doesn't consider this as varchar
[TX_ID] = dtx.TX_ID --Placeholder for this field so the #temp doesn't consider this as varchar
/* Validation Only */
--,[*Epic Cap Type] = IsNull(rtrim(cap_status.NAME),'')
--,[*Will be sent to vendor] = case when dtx.CHECK_ID is null
-- then 'No, because no check number is available'
-- else 'Yes'
-- end,
from CLARITY.dbo.CAP_PAY dtx
left join CLARITY.dbo.AP_CHECK ap_check
on ap_check.CHECK_ID = dtx.CHECK_ID
left join CLARITY.dbo.MEM_LIST cap_mem_list
on cap_mem_list.MEM_LIST_ID = dtx.MEM_LIST_ID
join CLARITY.dbo.MEM_LIST_DETAIL cap_mem_list_detail
on cap_mem_list_detail.MEM_LIST_ID = cap_mem_list.MEM_LIST_ID
join CLARITY.dbo.CAP_PAY_SPEC_AMT specialty_cap_amount
on specialty_cap_amount.TX_ID = dtx.TX_ID
left join CLARITY.dbo.ZC_SPEC_TX_TYPE special_cap
on special_cap.SPECIAL_TX_TYPE_C = dtx.SPECIAL_TX_TYPE_C
left join CLARITY.dbo.ZC_SPEC_TX_TYPE zdtx
on zdtx.SPECIAL_TX_TYPE_C = dtx.SPECIAL_TX_TYPE_C
left join CLARITY.dbo.CAP_TX_CNCT_INFO dtx20
on dtx20.TX_ID = dtx.TX_ID
left join CLARITY.dbo.PATIENT pat
on pat.pat_id = cap_mem_list_detail.MEMBER_LIST
left join CLARITY.dbo.CLARITY_VENDOR ven
on ven.VENDOR_ID = dtx.VENDOR_ID
left join CLARITY.dbo.MGRP mem_grp
on mem_grp.MGRP_ID = dtx.DIST_MGRP_ID
----- adding Specialty Capitiation PCP info ---------
outer apply (
select top 1 pcp.PCP_ID
from CLARITY.dbo.CVG_LOC_PCP pcp
where cap_mem_list_detail.MEMBER_LIST = pcp.MEMBER_ID
and cap_mem_list_detail.COVERAGE_LIST = pcp.COVERAGE_ID
and dtx.EFFECTIVE_DATE between pcp.EFF_DATE and isnull(pcp.TERM_DATE,'2078-12-31 00:00:00.000')
order by pcp.EFF_DATE desc
) pcp
left join CLARITY.dbo.CLARITY_SER cs
on cs.PROV_ID = pcp.PCP_ID
left join CLARITY.dbo.IDENTITY_SER_ID prov_npi
on prov_npi.PROV_ID = pcp.PCP_ID
and prov_npi.IDENTITY_TYPE_ID = '60' -- 60 is NPI (IDENTITY_ID_TYPE)
left join CLARITY.dbo.ZC_CAP_TX_TYPE dtxt
on dtxt.TX_TYPE_C = dtx.TX_TYPE_C
left join CLARITY.dbo.ZC_SPEC_TX_TYPE st
on st.SPECIAL_TX_TYPE_C = dtx.SPECIAL_TX_TYPE_C
left join CLARITY.dbo.ZC_CAP_TX_STATUS cap_status
on cap_status.TX_STATUS_C = dtx.TX_STATUS_C
left join CLARITY.dbo.COVERAGE_MEMBER_LIST cml
on cml.COVERAGE_ID = cap_mem_list_detail.COVERAGE_LIST
and cml.PAT_ID = cap_mem_list_detail.MEMBER_LIST
--and dtx.EFFECTIVE_DATE between cml.MEM_EFF_FROM_DATE and ISNULL(cml.MEM_EFF_TO_DATE, '12/31/2099')
left join CLARITY.dbo.COVERAGE cov
on cov.COVERAGE_ID = cap_mem_list_detail.COVERAGE_LIST
left join CLARITY.dbo.PLAN_GRP pg
on pg.PLAN_GRP_ID = cov.PLAN_GRP_ID
left join CLARITY.dbo.CLARITY_DIVISION cdiv
on cdiv.DIVISION_ID = pg.DIV_ID
left join ( Select TX_CANC_TX_ID
From clarity.DBO.Cap_Pay
where TX_CANC_TX_ID is not Null
and EFFECTIVE_DATE = @StartOfMonth
) Cancel
on cancel.TX_CANC_TX_ID = dtx.Tx_ID
--- SCFHP RATECODE REGION
OUTER APPLY ( SELECT TOP 1 CVA.NAME as [RateCode], CMAT.EFF_DATE , CMAT.TERM_DATE
FROM clarity.DBO.COVERAGE_MEM_ATTR CMAT
LEFT JOIN clarity.DBO.ZC_CVG_ATTR CVA
ON CVA.CVG_ATTR_C = CMAT.CVG_ATTR_C
WHERE CMAT.CVG_ATTR_C BETWEEN 43 AND 54
AND CMAT.COVERAGE_ID = CML.COVERAGE_ID
AND pat.PAT_ID = CML.PAT_ID
AND CMAT.EFF_DATE BETWEEN cml.MEM_EFF_FROM_DATE AND ISNULL(cml.MEM_EFF_TO_DATE, '12/31/2099')
and dtx.EFFECTIVE_DATE BETWEEN CMAT.EFF_DATE AND ISNULL(CMAT.TERM_DATE, '12/31/2099')
--OR CMAT.EFF_DATE BETWEEN C.SERVICE_START_DATE AND C.SERVICE_END_DATE)
ORDER BY CMAT.TERM_DATE DESC
) Ratecode
---------Member VMC MRN---------
outer apply (select top 1 vmc_mrn.IDENTITY_ID
from CLARITY.dbo.IDENTITY_ID vmc_mrn
where vmc_mrn.PAT_ID = cml.PAT_ID
and vmc_mrn.IDENTITY_TYPE_ID = '165'
order by vmc_mrn.line desc) vmc_mrn
---------Provider Pay-To (Vendor) Tax ID---------
outer apply (select top 1 ven_tin.TAX_ID
from CLARITY.dbo.VENDOR_TAX_ID ven_tin
where ven_tin.VENDOR_ID = dtx.VENDOR_ID
and ven_tin.TAX_ID_EFF_DATE <= getdate()
order by ven_tin.TAX_ID_EFF_DATE desc) ven_tin
where @Specialty_Cap_ID <> 'NONE'
and dtx.TX_TYPE_C= '3' -- Specialty Capitation
and (dtx.CHECK_ID is not NULL
)
and (DATEPART(MM,ap_check.CHECK_DATE) = (DATEPART(MM,@StartOfMonth)))
and (DATEPART(YYYY,ap_check.CHECK_DATE) = (DATEPART(YYYY,@StartOfMonth)))
and (
(@Specialty_Cap_ID <> 'NONE' and dtx.DIST_MGRP_ID = @Specialty_Cap_ID)
or @Specialty_Cap_ID = 'NONE'
)
and (
(@Epic_Vendor_ID <> 'ALL' and dtx.VENDOR_ID = @Epic_Vendor_ID)
or @Epic_Vendor_ID = 'ALL'
)
and (
(@RateCode <> 'ALL' and RateCode.RateCode = @RateCode)
or @RateCode = 'ALL'
)
order by
IsNull(rtrim(cml.MEM_NUMBER),''),
CONVERT(VARCHAR(10),CONVERT(DATETIME, dtx.EFFECTIVE_DATE),101),
IsNull(rtrim(cdiv.DIVISION_NAME),'')
/* Remove extra column that was used to display all coverages for cap under the same member */
ALTER TABLE #temp
DROP COLUMN [COVERAGE_ID], TX_ID
/* Get results and drop temp table */
SELECT count(*) as count FROM #temp
union
SELECT sum([CapAmount]) as sum FROM #temp
select * from #temp
order by
Member,
Cap_Date,
RateCode
Telegram: DB developers channel