USE[ccrsdb]

GO

 

/****** Object:  StoredProcedure [dbo].[get_unprocessed_providers]    Script Date: 3/25/2019 8:04:15 PM ******/

DROP PROCEDURE [dbo].[get_unprocessed_providers]

GO

 

/****** Object:  StoredProcedure [dbo].[get_unprocessed_providers]    Script Date: 3/25/2019 8:04:15 PM ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[get_unprocessed_providers]

AS

BEGIN

       SELECT

              claim.claim_key,

              prvdr.npiprovider_npi,

              mapping.ppms_keyccn_npi,

              prvdr.claim_provider_key,

              detail.service_date as 'service_date_from',

              NULL as 'service_date_to',

              'D' as claimType,

              ISNULL(mapping.ppms_key,0) as ppms_key

       FROM 

              DIM_VA_CLAIM claim

       join DIM_DENTAL_CLAIM dental on claim.claim_key = dental.claim_key

       join F_DENTAL_CLAIM_DETAILS detail on claim.claim_key = detail.claim_key

       join [ppms].[ccn_mapping]mapping on claim.ccn_region = mapping.provider_key

       join CLAIM_PROVIDER prvdr on claim.claim_key = prvdr.claim_key AND prvdr.provider_type = 'RENDERING'

       left join decision dcsn on detail.claim_detail_key = dcsn.claim_detail_key and dcsn.decision NOT IN ('REJECTED', 'DENIED')

       WHERE

              prvdr.ppms_is_provider_active  IS NULL

       AND prvdr.ppms_is_provider_on_leie IS NULL

       AND prvdr.ppms_validation_date IS NULL  

       GROUP BY

              claim.claim_key,

              prvdr.npi,

              mapping.ppms_key,

              prvdr.claim_provider_key,

              detail.service_date,

              mapping.ppms_key



       UNION ALL



       SELECT

              claim.claim_key,

              prvdr.npiprovider_npi,

              mapping.ppms_keyccn_npi,

              prvdr.claim_provider_key,

              detail.service_date as 'service_date_from',

              NULL as 'service_date_to',

              'I' as claimtype,

              ISNULL(mapping.ppms_key,0) as ppms_key

       FROM 

              DIM_VA_CLAIM claim

       join DIM_INSTITUTIONAL_CLAIM institut on claim.claim_key = institut.claim_key

       join F_INSTITUTIONAL_MEDICAL_CLAIM_DETAILS detail on claim.claim_key = detail.claim_key

       join[ppms].[ccn_mapping]mapping on claim.ccn_region = mapping.provider_key

       join CLAIM_PROVIDER prvdr on claim.claim_key = prvdr.claim_key AND prvdr.provider_type = 'RENDERING'

       left join decision dcsn on detail.claim_detail_key = dcsn.claim_detail_key and dcsn.decision NOT IN ('REJECTED', 'DENIED')

       WHERE

              prvdr.ppms_is_provider_active  IS NULL

       AND prvdr.ppms_is_provider_on_leie IS NULL

       AND prvdr.ppms_validation_date IS NULL  

       GROUP BY

              claim.claim_key,

              prvdr.npi,

              mapping.ppms_key,

              prvdr.claim_provider_key,

              detail.service_date,

              mapping.ppms_key

       

       UNION ALL



       SELECT

              claim.claim_key,

              prvdr.npiprovider_npi,

              mapping.ppms_keyccn_npi,

              prvdr.claim_provider_key,

              detail.service_date_from,

              detail.service_date_to,

              'P' as claimtype,

              ISNULL(mapping.ppms_key,0)ppms_key

       FROM 

              DIM_VA_CLAIM claim

       join DIM_PROFESSIONAL_CLAIM prof on claim.claim_key = prof.claim_key

       join F_PROFESSIONAL_MEDICAL_CLAIM_DETAILS detail on claim.claim_key = detail.claim_key

       join [ppms].[ccn_mapping]mapping on claim.ccn_region = mapping.provider_key

       join CLAIM_PROVIDER prvdr on claim.claim_key = prvdr.claim_key AND prvdr.provider_type = 'RENDERING'

       left join decision dcsn on detail.claim_detail_key = dcsn.claim_detail_key and dcsn.decision NOT IN ('REJECTED', 'DENIED')

       WHERE

              prvdr.ppms_is_provider_active  IS NULL

       AND prvdr.ppms_is_provider_on_leie IS NULL

       AND prvdr.ppms_validation_date IS NULL  

       GROUP BY

              claim.claim_key,

              prvdr.npi,

              mapping.ppms_key,

              prvdr.claim_provider_key,

              detail.service_date_from,

              detail.service_date_to,

              mapping.ppms_key

END;

GO
