/*
  2025-09-04 Naomi created the procedure
  execute im_automation.accent_changes
  
*/

CREATE OR ALTER PROCEDURE im_automation.accent_changes
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @last_run DATETIME;

    SELECT @last_run = CAST(MAX(eventdatetime) AS DATE)
    FROM log.processevent
    WHERE processname = 'PDM Daily Automation'
          AND eventtypecode = 'Automation Daily File';

    SET @last_run = ISNULL(@last_run, CAST(CURRENT_TIMESTAMP AS DATE));

    DROP TABLE IF EXISTS #FirstResult;
    DECLARE @message VARCHAR(100) = 'Updates started at ' + CAST(CURRENT_TIMESTAMP AS VARCHAR(100));

    RAISERROR(@message, 0, 1) WITH NOWAIT;

    SELECT f.record_sequence,
           f.snapshot_date,
           f.npi_hk,
           f.tin_hk,
           f.caresite_hk,
           f.specialty_hk,
           f.specialty_npi_lhk,
           f.specialty_npi_caresite_lhk,
           f.npi_caresite_lhk,
           f.npi_tin_lhk,
           f.service_address_hk,
           f.remitt_address_hk,
           f.npi_service_address_lhk,
           f.npi_remitt_address_lhk,
           f.caresite_service_address_lhk,
           f.caresite_remitt_address_lhk,
           f.service_phone_hk,
           f.service_fax_hk,
           f.caresite_address,
           f.caresite_service_phone_lhk,
           f.caresite_service_fax_lhk,
           f.npi_tin_caresite_lhk,
           f.address_hlhk,
           f.tin_number,
           f.npi,
           CASE
               WHEN f.prov_group = 'Y' THEN
                   LEFT(f.clinic_name, 39)
               ELSE
                   LEFT(f.prov_last_name, 18)
           END AS Prov_Last_Name,
           f.prov_first_name,
           f.prov_mi,
           LEFT(f.clinic_name, 39) AS clinic_Name,
           CASE
               WHEN f.Status = 'Active' THEN
                   f.snapshot_date
               ELSE
                   NULL
           END AS [OHCS Change Date],
           CASE
               WHEN COALESCE(f.client_eff_date, '01/01/1999') <= '06/26/2019' THEN
                   '06/26/2019'
               ELSE
                   client_eff_date
           END AS [OHCS Eff Date],

           /*
           
           If record Status = Termed:
             Group or Solo: Not Applicable.
             Rendering: Store latest "OHCS Term Date" from OHCS daily file 
             or date NPI disappeared from OHBS daily file if NPI had Primary Contract Relationship = Clinician only. 
             Join on NPI/TinNumber. 
           
           */
           CASE
               WHEN f.Status = 'Termed' THEN
                   f.status_date
               ELSE
                   NULL
           END AS [OHCS Term Date],
           f.specialty,
           f.sub_specialty,
           LEFT(f.remit_address1, 30) AS remit_address1,
           f.remit_address2,
           f.remit_city,
           f.remit_state,
           f.remit_zip,
           LEFT(f.loc_address1, 30) AS loc_address1,
           f.loc_address2,
           f.loc_city,
           f.loc_state,
           f.loc_zip,
           f.loc_county,
           f.license_number,
           f.license_state,
           f.license_issue_date,
           f.acn_prov_id,
           f.provider_id,
           f.office_location_id,
           f.main_status,
           f.sub_status,
           f.status_date,
           f.loc_eff_date,
           f.loc_term_date,
           f.tin_eff_date,
           f.tin_term_date,
           f.prov_phone,
           f.prov_fax,
           f.gender,
           f.client_term_date,
           f.cred_status_eff_date,
           f.Status,
           CAST(CASE
                    WHEN f.prov_group = 'Y' THEN
                        'Group'
                    WHEN f.prov_group = 'N'
                         AND
                         (
                             SELECT COUNT(DISTINCT (npi))
                             FROM im_automation.stg_accent_snapshot f2
                             WHERE f2.tin_number = f.tin_number
                         ) = 1 THEN
                        'Solo'
                    -- WHEN f.prov_group = 'N' AND (SELECT COUNT(DISTINCT(NPI)) FROM im_automation.stg_accent_snapshot f2 WHERE f2.tin_number = f.tin_number) > 1 THEN 'Rendering'
                    ELSE
                        'Rendering'
                END AS VARCHAR(10)) AS Provider_Type,
           CAST('No' AS VARCHAR(3)) AS Social_Group,
           CAST('' AS VARCHAR(20)) AS Request_Type,
           CAST('' AS VARCHAR(100)) AS Change_Type,
           CAST('' AS VARCHAR(MAX)) AS Exclusion_Reason
    INTO #FirstResult
    FROM im_automation.stg_accent_snapshot f;

    UPDATE f
    SET f.Social_Group = 'Yes'
    FROM #FirstResult f
    WHERE Provider_Type = 'Rendering'
          AND specialty IN ( 'MT', 'DC', 'LAC' )
          AND NOT EXISTS
    (
        SELECT *
        FROM #FirstResult f2
        WHERE f2.tin_number = f.tin_number
              AND f2.Status = 'Active'
    );

    UPDATE f
    SET Request_Type = 'Add',
        Change_Type = ''
    FROM #FirstResult f
        INNER JOIN rv.hub_tin h
            ON f.tin_number = h.tin
    WHERE h.source = 'ACCENT'
          AND h.load_date >= @last_run
          AND f.[Status] = 'Active'
          AND f.Provider_Type IN ( 'Group', 'Solo' ); -- New TIN appears;

    UPDATE f
    SET Request_Type = 'Add',
        Change_Type = ''
    FROM #FirstResult f
        INNER JOIN rv.hub_npi h -- new NPI
            ON f.npi = h.npi
    WHERE h.source = 'ACCENT'
          AND h.load_date >= @last_run
          AND f.[Status] = 'Active'
          AND f.Provider_Type = 'Rendering'; -- New NPI;



    ;
    WITH cte
    AS (SELECT f.*,
               ROW_NUMBER() OVER (PARTITION BY f.tin_number, f.npi ORDER BY [f].[OHCS Term Date] DESC) AS RN
        FROM #FirstResult f
        WHERE f.Provider_Type = 'Rendering'
              AND [OHCS Term Date] >= [OHCS Eff Date]
              AND 'Termed'=ALL
              (
                  SELECT Status
                  FROM #FirstResult f2
                  WHERE f.tin_number = f2.tin_number
				  AND f.npi = f2.npi
                        AND f2.npi = f.npi
                        AND Provider_Type = 'Rendering'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
              )
    -- 	Rendering: all "Rendering" rows for NPI/TinNumber have "Termed" status
    )
    UPDATE f
    SET Request_Type = CASE WHEN  NOT EXISTS(SELECT 1 FROM #FirstResult f1 WHERE f1.tin_number <> f.tin_number
			   AND f.npi = f1.npi) 
			   OR NOT EXISTS (SELECT 1 FROM #FirstResult f1 WHERE f1.npi = f.npi)
			   
			   AND EXISTS (SELECT 1 FROM stg.facets_full fu WHERE fu.taxid = f.tin_number) THEN ''
			   WHEN EXISTS (SELECT * FROM stg.facets_full fu WHERE fu.taxid <> f.tin_number) OR 
			        EXISTS (SELECT * FROM stg.facets_full fu WHERE fu.taxid = f.tin_number
					       AND fu.primarycontractedrelationship <> 'Clinician') THEN 'PARITAL TERM'
						   WHEN NOT EXISTS (SELECT 1 FROM stg.facets_full fu WHERE fu.npi = f.npi)
						   AND EXISTS(SELECT 1 FROM #FirstResult f1 WHERE f1.tin_number <> f.tin_number
			   AND f.npi = f1.npi) THEN 'PARTIAL TERM'

			   WHEN NOT EXISTS (SELECT 1 FROM stg.facets_full fu WHERE fu.npi = f.npi) AND NOT EXISTS(SELECT 1 FROM #FirstResult f2 WHERE f2.npi= f.npi)

			   THEN 'FULL TERM' END,

					       
        Change_Type = ''
    FROM #FirstResult f
        INNER JOIN cte
            ON cte.record_sequence = f.record_sequence
               AND cte.RN = 1
			  ;



    ;WITH cte
    AS (SELECT *,
               ROW_NUMBER() OVER (PARTITION BY f.tin_number ORDER BY [f].[OHCS Term Date] DESC) AS RN
        FROM #FirstResult f
        WHERE Provider_Type = 'Solo'
              AND [OHCS Term Date] >= [OHCS Eff Date]
              AND 'Termed'=ALL
              (
                  SELECT Status
                  FROM #FirstResult f2
                  WHERE f.tin_number = f2.tin_number
                        AND f.Provider_Type = 'Solo'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
              )
    -- 	Solo: rows for TinNumber have "Termed" status
    )
    UPDATE f
    SET Request_Type = 'Term',
        Change_Type = ''
    FROM #FirstResult f
        INNER JOIN cte
            ON cte.record_sequence = f.record_sequence
               AND cte.RN = 1 
			   AND NOT EXISTS(SELECT 1 FROM stg.facets_full f1 WHERE f1.taxid = f.tin_number);

-- Need to implement more complex logic:

/*
All Group rows for TIN have "Termed" status and TIN has renderings with only specialties PT/OT/SLP:
Send as FULL TERM Request:
   All Group and corrolating renderings will be sent for Term
      
All Group rows for TIN have "Termed" status and has renderings with specialties DC/LAC/MT:
Send as FULL TERM Request:
      All Group and corrolating renderings with PT/OT/SLP will be sent for Term
      All "Active" Renderings with TIN, with specialties DC/LAC/MT will need to be excluded to nPRL with reason "Social Grouping Required" reason.


*/
    ;WITH cte
    AS (SELECT f.*,
               ROW_NUMBER() OVER (PARTITION BY f.tin_number, npi ORDER BY f.[OHCS Term Date]) AS RN
        FROM #FirstResult f
        WHERE f.Provider_Type = 'Group'
              AND [OHCS Term Date] >= [OHCS Eff Date]
              AND 'Termed'=ALL
              (
                  SELECT Status
                  FROM #FirstResult f2
                  WHERE f.tin_number = f2.tin_number
                        AND Provider_Type = 'Group'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
              )
    -- 	Group: all "Group" rows for NPI/TinNumber have "Termed" status
    )
    UPDATE f
    SET f.Request_Type = 'Term'
    FROM #FirstResult f
        INNER JOIN cte
            ON cte.RN = 1
               AND f.tin_number = cte.tin_number
               AND f.npi = cte.npi
               AND f.[OHCS Term Date] = cte.[OHCS Term Date]
			   AND NOT EXISTS(SELECT 1 FROM stg.facets_full f1 WHERE f1.taxid = f.tin_number);



    -- Changes -- Address Add
    UPDATE f
    SET Request_Type = 'Change',
        Change_Type = 'Address Add'
    FROM #FirstResult f
        INNER JOIN rv.hub_address h
            ON f.service_address_hk = h.address_hk
               OR f.remitt_address_hk = h.address_hk
        INNER JOIN rv.lnk_caresite_address lnk
            ON lnk.address_hk = h.address_hk
        INNER JOIN rv.hub_caresite cs
            ON cs.caresite_hk = lnk.caresite_hk
               AND cs.tin = f.tin_number
    WHERE f.Provider_Type IN ( 'GROUP', 'SOLO' )
          AND f.[Status] = 'Active'
          AND h.Source = 'ACCENT'
          AND h.load_date >= @last_run;



  UPDATE f
    SET Request_Type = 'Change',
        Change_Type = 'Address Add'
    FROM #FirstResult f
        INNER JOIN rv.hub_address h
            ON f.service_address_hk = h.address_hk
               OR f.remitt_address_hk = h.address_hk
        INNER JOIN rv.lnk_caresite_address lnk
            ON lnk.address_hk = h.address_hk
        INNER JOIN rv.hub_caresite cs
            ON cs.caresite_hk = lnk.caresite_hk
               AND cs.tin = f.tin_number 
        INNER JOIN rv.hub_npi npi ON npi.npi_hk = f.npi_hk

    WHERE f.Provider_Type IN ( 'Rendering' )
          AND f.[Status] = 'Active'
          AND h.Source = 'ACCENT'
          AND h.load_date >= @last_run;


    UPDATE f
    SET Request_Type = 'Change',
        Change_Type = 'Name Change'
    FROM rv.sat_pdm_practitioner_accent a
        INNER JOIN #FirstResult f
            ON f.npi_hk = a.npi_hk
    WHERE a.source = 'ACCENT'
          AND f.Provider_Type = 'Rendering'
          AND f.[Status] = 'Active'
          AND a.load_date >= @last_run
          AND EXISTS
    (
        SELECT 1
        FROM rv.sat_pdm_practitioner_accent b
        WHERE a.npi_hk = b.npi_hk
              AND b.load_date < a.load_date -- it is only loaded into that table when there is a different record
    );

    UPDATE f
    SET Request_Type = 'Change',
        Change_Type = 'Name Change'
    FROM rv.sat_pdm_solo_accent v
        INNER JOIN #FirstResult f
            ON f.npi_tin_lhk = v.npi_tin_lhk
    WHERE v.source = 'ACCENT'
          AND f.Provider_Type = 'Solo'
          AND f.[Status] = 'Active'
          AND v.load_date >= @last_run
          AND EXISTS
    (
        SELECT 1
        FROM rv.sat_pdm_solo_accent s
        WHERE s.npi_tin_lhk = v.npi_tin_lhk
              AND s.load_date < v.load_date -- it is only loaded into that table when there is a different record
    );

    UPDATE f
    SET f.Exclusion_Reason = CAST(CASE
                                      WHEN f.Request_Type IN ( 'Add', 'Change' ) THEN
                                          CASE
                                              WHEN f.loc_address1 LIKE '%at home%'
                                                   OR f.loc_address1 LIKE '%in home%'
                                                   OR f.loc_address1 LIKE '%PO Box%'
                                                   OR f.loc_address1 LIKE '%telehealth%'
                                                   OR f.loc_address1 LIKE '%telemental%'
                                                   OR f.loc_address1 LIKE '%virtual%' THEN
                                                  ', Invalid PLSV Address'
                                              ELSE
                                                  ''
                                          END + CASE
                                                    WHEN f.remit_address1 = '' THEN
                                                        ', No BILL Address'
                                                    ELSE
                                                        ''
                                                END + CASE
                                                          WHEN f.loc_address1 = '' THEN
                                                              ', No PLSV Address'
                                                          ELSE
                                                              ''
                                                      END
                                          + CASE
                                                WHEN f.Provider_Type NOT IN ( 'Group', 'Solo', 'Rendering' ) THEN
                                                    ', No ProvType'
                                                ELSE
                                                    ''
                                            END + CASE
                                                      WHEN NOT EXISTS
                                                               (
                                                                   SELECT sp.specialty,
                                                                          tx.taxonomy
                                                                   FROM rv.lnk_specialty_taxonomy lnk
                                                                       INNER JOIN rv.hub_specialty sp
                                                                           ON lnk.specialty_hk = sp.specialty_hk
                                                                       INNER JOIN rv.hub_taxonomy tx
                                                                           ON tx.taxonomy_hk = lnk.taxonomy_hk
                                                                   WHERE sp.specialty = f.specialty
                                                               ) THEN
                                                          ', No Specialty Match'
                                                      ELSE
                                                          ''
                                                  END
                                      ELSE
                                          ''
                                  END AS VARCHAR(MAX))
    FROM #FirstResult f;



    SET @message = 'Updates done at ' + CAST(CURRENT_TIMESTAMP AS VARCHAR(100));

    RAISERROR(@message, 0, 1) WITH NOWAIT;
    /**/


    DROP TABLE IF EXISTS #Additions;
    DROP TABLE IF EXISTS #Terminations;
    DROP TABLE IF EXISTS #Changes;

    SET @message = '
	First selects started at ' + CAST(CURRENT_TIMESTAMP AS VARCHAR(100));

    RAISERROR(@message, 0, 1) WITH NOWAIT;

    SELECT record_sequence,
           snapshot_date,
           npi_hk,
           tin_hk,
           Exclusion_Reason,
           tin_number,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Additions
    FROM #FirstResult
    WHERE Request_Type = 'Add';

    SELECT record_sequence,
           snapshot_date,
           npi_hk,
           tin_hk,
           Exclusion_Reason,
           tin_number,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Changes
    FROM #FirstResult
    WHERE Request_Type = 'Change';

    SET @message = 'First selects ended at ' + CAST(CURRENT_TIMESTAMP AS VARCHAR(100));

    RAISERROR(@message, 0, 1) WITH NOWAIT;

    SET STATISTICS TIME ON;
    SET STATISTICS IO ON;

    CREATE NONCLUSTERED INDEX idxNpi ON #FirstResult (npi);

;   WITH cteLastTermDate
AS (SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [a].[OHCS Term Date]) AS dt,
           a.npi
    FROM #FirstResult a
    UNION ALL
    SELECT MAX(hn.load_date) AS dt,
           npi
    FROM rv.hub_npi hn
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM stg.stg_facets_full f
        WHERE f.npi_hk = hn.npi_hk
              AND f.primary_contracted_relationship = 'Clinician Only'
    )
          AND hn.source = 'FACETS'
    GROUP BY hn.npi)
    SELECT record_sequence,
           snapshot_date,
           tin_number,
           Exclusion_Reason,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           CAST(CASE
                    WHEN Status = 'Termed' THEN
                        CASE
                            WHEN Provider_Type IN ( 'Group', 'Solo' ) THEN
                                [OHCS Term Date]
                            WHEN Provider_Type = 'Rendering' THEN
                            (
                                SELECT MAX(dt) FROM cteLastTermDate c WHERE c.npi = cte.npi
                            )
                        END
                END AS DATE) AS latest_term_date,
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Terminations
    FROM #FirstResult cte
    WHERE Request_Type = 'Term';

    WITH cteEarliestEffectiveDate (dt, npi, tin_number)
    AS (SELECT FIRST_VALUE(f.[OHCS Eff Date]) OVER (PARTITION BY f.npi, f.tin_number ORDER BY f.[OHCS Eff Date]) AS dt,
               f.npi, f.tin_number
        FROM #FirstResult f
        UNION ALL
        SELECT MIN(f.firstcontracteffectivedate) AS dt,
               f.npi, f.taxid
        FROM stg.facets_full f
        GROUP BY npi,f.taxid),
cteEffectiveDate AS (SELECT MAX(f.[OHCS Eff Date]) 
OVER (PARTITION BY npi,f.tin_number ORDER BY f.[OHCS Eff Date]) AS dt,
               npi, f.tin_number
        FROM #FirstResult f
        UNION ALL
        SELECT MIN(f.firstcontracteffectivedate) AS dt,
               npi, f.taxid
        FROM stg.facets_full f
		WHERE f.primarycontractedrelationship = 'Clinician Only'
        GROUP BY npi, f.taxid)
  
    SELECT 'Automation OHCS' AS source,
           cte.Request_Type,
           cte.Change_Type,
           cte.[Exclusion_Reason],
           -- 'Professional' AS category,
           CAST(cte.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte.[Status] AS VARCHAR(10)) AS [status],
           CAST(cte.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte.Provider_Type IN ( 'Group', 'Solo' ) THEN
                        (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.tin_number = cte.tin_number
                    )
                    WHEN cte.Provider_Type = 'Rendering' THEN
                    (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.npi = cte.npi
                    )
                END AS DATE) AS earliest_effective_date,
           CAST([cte].[OHCS Change Date] AS DATE) AS change_date,
           CAST(cte.[OHCS Term Date] AS DATE) AS term_date,
           CAST(NULL AS DATE) AS latest_term_date,
           cte.tin_number,
           cte.npi,
           CAST(cte.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte.Prov_Last_Name AS VARCHAR(18)) AS provider_last_name,
           CAST(cte.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte.clinic_Name AS VARCHAR(39)) AS clinic_name,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_specialty) OVER()
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_provider_type) OVER()
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.taxonomy) OVER()
                END AS VARCHAR(10)) AS t15_taxonomy,
           CAST(CASE WHEN cte.loc_state = cte.license_state AND cte.Provider_Type IN ('Rendring', 'Solo')
		   
		   
		   THEN MIN(cte.license_number) OVER() ELSE '' END AS VARCHAR(15)) AS license_number,
           CASE WHEN cte.Provider_Type IN ('Rendring', 'Solo') AND cte.loc_state = cte.license_state
		   AND cte.license_number = MIN(cte.license_number) OVER()
		   
		   THEN CAST(cte.license_state AS VARCHAR(50)) ELSE '' END AS license_state,
           CASE WHEN cte.Provider_Type IN ('Rendring', 'Solo') AND cte.loc_state = cte.license_state
		   AND cte.license_number = MIN(cte.license_number) OVER()
		   
		   THEN CAST(cte.license_issue_date AS DATE) ELSE '' END AS license_issue_date,
           CAST(cte.loc_address1 AS VARCHAR(30)) AS svc_address_line1,
           CAST(cte.loc_address2 AS VARCHAR(30)) AS svc_address_line2,
           CAST(cte.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte.loc_zip AS VARCHAR(9)) AS svc_zip,
           CAST(cte.remit_address1 AS VARCHAR(50)) AS remit_address_line1,
           CAST(cte.remit_address2 AS VARCHAR(50)) AS remit_address_line2,
           CAST(cte.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte.remit_zip AS VARCHAR(9)) AS remit_zip,
		   CASE WHEN cte.Request_Type = 'Add' AND cte.Provider_Type = 'Rendering' THEN
		   (SELECT MAX(dt) OVER() FROM cteEffectiveDate c 
		   WHERE c.npi = cte.npi AND c.tin_number = cte.tin_number) ELSE NULL END AS 
		  [SOcial Grouping Effective Date],
		   CASE  WHEN  EXISTS (SELECT 1 FROM #FirstResult f 
		  WHERE f.npi = cte.npi AND f.tin_number = cte.tin_number) AND
          EXISTS (SELECT 1 FROM stg.facets_full f WHERE f.npi = cte.npi AND f.taxid = cte.tin_number) THEN 'Y' 
		  ELSE 'N' END AS [Dually COntracted]
    FROM #Additions cte
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte.specialty = tx.specialty
    UNION ALL
    SELECT 'Automation OHCS' AS source,
           cte1.Request_Type,
           cte1.Change_Type,
           cte1.Exclusion_Reason,
           CAST(cte1.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte1.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte1.[Status] AS VARCHAR(10)) AS [status],
           CAST(cte1.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte1.Provider_Type IN ( 'Group', 'Solo' ) THEN
                        (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.tin_number = cte1.tin_number
                    )
                    WHEN cte1.Provider_Type = 'Rendering' THEN
                    (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.npi = cte1.npi
                    )
                END AS DATE) AS earliest_effective_date,
           CAST([cte1].[OHCS Change Date] AS DATE) AS change_date,
           CAST(cte1.[OHCS Term Date] AS DATE) AS term_date,
           CAST(NULL AS DATE) AS latest_term_date,
           cte1.tin_number,
           cte1.npi,
           CAST(cte1.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte1.Prov_Last_Name AS VARCHAR(18)) AS provider_last_name,
           CAST(cte1.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte1.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte1.clinic_Name AS VARCHAR(39)) AS clinic_name,
          
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_specialty) OVER() ELSE ''
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_provider_type) OVER() ELSE ''
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.taxonomy) OVER() ELSE ''
                END AS VARCHAR(10)) AS t15_taxonomy,
            CAST(CASE WHEN cte1.loc_state = cte1.license_state AND cte1.Provider_Type IN ('Rendring', 'Solo')
		   
		   
		   THEN MIN(cte1.license_number) OVER() ELSE '' END AS VARCHAR(15)) AS license_number,
           CASE WHEN cte1.Provider_Type IN ('Rendring', 'Solo') AND cte1.loc_state = cte1.license_state
		   AND cte1.license_number = MIN(cte1.license_number) OVER()
		   
		   THEN CAST(cte1.license_state AS VARCHAR(50)) ELSE '' END AS license_state,
           CASE WHEN cte1.Provider_Type IN ('Rendring', 'Solo') AND cte1.loc_state = cte1.license_state
		   AND cte1.license_number = MIN(cte1.license_number) OVER()
		   
		   THEN CAST(cte1.license_issue_date AS DATE) ELSE '' END AS license_issue_date,
		   CAST(cte1.loc_address1 AS VARCHAR(30)) AS svc_address_line1,
           CAST(cte1.loc_address2 AS VARCHAR(30)) AS svc_address_line2,
           CAST(cte1.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte1.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte1.loc_zip AS VARCHAR(9)) AS svc_zip,
           CAST(cte1.remit_address1 AS VARCHAR(30)) AS remit_address_line1,
           CAST(cte1.remit_address2 AS VARCHAR(30)) AS remit_address_line2,
           CAST(cte1.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte1.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte1.remit_zip AS VARCHAR(9)) AS remit_zip,
		    CASE WHEN cte1.Request_Type = 'Add' AND cte1.Provider_Type = 'Rendering' THEN
		   (SELECT MAX(dt) OVER() FROM cteEffectiveDate c 
		   WHERE c.npi = cte1.npi AND c.tin_number = cte1.tin_number) ELSE NULL END AS 
		  [SOcial Grouping Effective Date],
		  CASE  WHEN  EXISTS (SELECT 1 FROM #FirstResult f 
		  WHERE f.npi = cte1.npi AND f.tin_number = cte1.tin_number) AND
          EXISTS (SELECT 1 FROM stg.facets_full f WHERE f.npi = cte1.npi AND f.taxid = cte1.tin_number) THEN 'Y' 
		  ELSE 'N' END AS [Dually COntracted]
		  
		
    FROM #Changes cte1
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte1.specialty = tx.specialty
               AND tx.source = 'OHCS'
    UNION ALL
    SELECT 'Automation OHCS' AS source,
           cte2.Request_Type,
           cte2.Change_Type,
           cte2.[Exclusion_Reason],
           CAST(cte2.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte2.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte2.Status AS VARCHAR(10)) AS status,
           CAST(cte2.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte2.Provider_Type IN ( 'Group', 'Solo' ) THEN
                        (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.tin_number = cte2.tin_number
                    )
                    WHEN cte2.Provider_Type = 'Rendering' THEN
                    (
                        SELECT MIN(dt) FROM cteEarliestEffectiveDate ct WHERE ct.npi = cte2.npi
                    )
                END AS DATE) AS earliest_effective_date,
           CAST([cte2].[OHCS Change Date] AS DATE) AS change_date,
           CAST(cte2.[OHCS Term Date] AS DATE) AS term_date,
           cte2.latest_term_date,
           cte2.tin_number,
           cte2.npi,
           CAST(cte2.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte2.Prov_Last_Name AS VARCHAR(18)) AS provider_last_name,
           CAST(cte2.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte2.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte2.clinic_Name AS VARCHAR(39)) AS clinic_name,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_specialty) OVER()
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_provider_type) OVER()
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.taxonomy) OVER()
                END AS VARCHAR(10)) AS t15_taxonomy,
            CAST(CASE WHEN cte2.loc_state = cte2.license_state AND cte2.Provider_Type IN ('Rendring', 'Solo')
		   
		   
		   THEN MIN(cte2.license_number) OVER() ELSE '' END AS VARCHAR(15)) AS license_number,
           CASE WHEN cte2.Provider_Type IN ('Rendring', 'Solo') AND cte2.loc_state = cte2.license_state
		   AND cte2.license_number = MIN(cte2.license_number) OVER()
		   
		   THEN CAST(cte2.license_state AS VARCHAR(50)) ELSE '' END AS license_state,
           CASE WHEN cte2.Provider_Type IN ('Rendring', 'Solo') AND cte2.loc_state = cte2.license_state
		   AND cte2.license_number = MIN(cte2.license_number) OVER()
		   
		   THEN CAST(cte2.license_issue_date AS DATE) ELSE '' END AS license_issue_date,
           CAST(cte2.loc_address1 AS VARCHAR(30)) AS svc_address_line1,
           CAST(cte2.loc_address2 AS VARCHAR(30)) AS svc_address_line2,
           CAST(cte2.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte2.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte2.loc_zip AS VARCHAR(9)) AS svc_zip,
           CAST(cte2.remit_address1 AS VARCHAR(30)) AS remit_address_line1,
           CAST(cte2.remit_address2 AS VARCHAR(30)) AS remit_address_line2,
           CAST(cte2.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte2.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte2.remit_zip AS VARCHAR(9)) AS remit_zip,
		   CASE WHEN cte2.Request_Type = 'Add' AND cte2.Provider_Type = 'Rendering'
		   THEN
		   (SELECT MAX(dt) OVER() FROM cteEffectiveDate c 
		   WHERE c.npi = cte2.npi AND c.tin_number = cte2.tin_number) ELSE NULL END AS 
		  [Social Grouping Effective Date],
		   CASE  WHEN  EXISTS (SELECT 1 FROM #FirstResult f 
		  WHERE f.npi = cte2.npi AND f.tin_number = cte2.tin_number) AND
          EXISTS (SELECT 1 FROM stg.facets_full f WHERE f.npi = cte2.npi AND f.taxid = cte2.tin_number) THEN 'Y' 
		  ELSE 'N' END AS [Dually Contracted]
		  		   
    FROM #Terminations cte2
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte2.specialty = tx.specialty
               AND tx.source = 'OHCS'
    OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')); --OPTION (FAST 10);

    SET STATISTICS TIME OFF;
    SET STATISTICS IO OFF;

END;