Database

This error arises if you attempt to restore a database from a backup created on a later version of SQL Server to an earlier version of SQL Server. You can only restore to the same or a later version of SQL Server.

SELECT
    vw_OwnerXML5010.Owner,
    vw_OwnerXML5010.Billing_EIN AS TP_EIN,
    vw_OwnerXML5010.TP_Name AS TP_NAME,
    vw_OwnerXML5010.County_Code AS County_Code,
    (
   SELECT
   (
    SELECT
        vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
        vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
    (
    Select 
        vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
        vw_ServicesXML5010.Modifier1 As "Modifier1",
        vw_ServicesXML5010.Modifier2 AS "Modifier2",
        vw_ServicesXML5010.Modifier3 AS "Modifier3",
        vw_ServicesXML5010.Modifier4 AS "Modifier4",
        vw_ServicesXML5010.From_Date AS "From_Date",
        vw_ServicesXML5010.To_Date AS "To_Date",
        vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
        vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
        vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
    FROM 
    vw_ServicesXML5010 
    WHERE
        vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI and 
        vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN  
    FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
    ) 
    FROM 
    vw_ServiceFacilityXML5010
    WHERE
        vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
    FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE   
)
FROM 
    dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
        DISTINCT 'DMH' AS [Owner]
        ,'' AS [Billing_NPI]
        ,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
        ,'' AS DirectIndicator
        ,CO.PK_Geographic_Location_Code AS [County_Code]
        ,CO.County_Name AS [TP_Name]
    FROM
        dbo.LegalEntity LE<br />
        INNER JOIN dbo.county CO
            ON LE.FKCountyLKPId = CO.PKCountyId
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND LEFT(LegalEntityNbr,3) IN ('000')
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO

CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SELECT
    distinct VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    , 'true' AS [County_Owned_Indicator]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) IN ('000', 'AFC')
    AND LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
    UNION ALL<br />
SELECT
    distinct    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('000', 'AFC','HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode

GO

CREATE VIEW [dbo].[vw_ServicesXML5010]
AS
SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code   
    ,ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '') AS [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] 
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.FKServFuncCatMCModeOfServiceId = SFC.PKServFuncCatMCModeOfServiceId
    AND SP.Modifier1 <> 'HK'    
    UNION ALL   
    SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code
    ,CASE
        WHEN (SP.ProcedureCode = 'H2015' or SP.ProcedureCode = 'T1017')AND SP.Modifier1 = 'HK' --all Katie A. Services start on 20130101
        THEN '20130101' 
        ELSE
            ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '')
        END [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] -- end date is not changing for Katie A. services. Just eliminate the rows that have an end date
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode    
    AND (SP.Modifier1 = 'HK' and (CS.MCEndDate is null or  (CS.MCBeginDate >= '20130101' or  CS.MCEndDate > '20130101')))
GO