/*
  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)) + ', last run: '
          + CAST(@last_run AS VARCHAR(20));

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

    SELECT f.record_sequence,
           f.snapshot_date,
           f.npi_hk,
           f.tin_hk,
           f.service_address_hk,
           f.remitt_address_hk,
           f.npi_tin_lhk,
           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_group,
           f.prov_first_name,
           f.prov_mi,
           LEFT(f.clinic_name, 39) AS clinic_Name,
           CASE
               WHEN f.[Status] = 'Active' THEN
                   CAST([f].[snapshot_date] AS 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,

                                                                  /*   /DERIVED*	Social Group Partner	Yes	If Social Grouping Value is Yes, populate applicable parnter	"Partners:
                                                                  OHBS
                                                                  OHCS
                                                                  UHN
                                                                  Spectera
                                                                  
                                                                  "
                                                                  */
           CAST('OHCS' AS VARCHAR(30)) AS [Social Group Partner], -- may need to add other partners
           CAST('' AS VARCHAR(20)) AS Request_Type,
           CAST('' AS VARCHAR(100)) AS Change_Type,
           CAST('' AS VARCHAR(MAX)) AS Exclusion_Reason,
           CAST('N' AS VARCHAR(1)) AS [Dually Contracted],
           CAST(NULL AS DATE) AS [Earliest Eff Date],
           CAST(NULL AS DATE) AS [Social Grouping Effective Date],
           CAST(NULL AS DATE) AS [Latest Term Date],
           CAST(NULL AS DATE) AS [SG Latest Term Date]
    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'
    );
    /*OHBS - TIN has Contract relation of "Clinician" with multiple renderings */
    UPDATE f
    SET f.Social_Group = 'Yes'
    FROM #FirstResult f
    WHERE EXISTS
    (
        SELECT 1
        FROM stg.facets_full ff
        WHERE ff.primarycontractedrelationship = 'Clinician'
              AND ff.typeofprovider = 'P'
        HAVING COUNT(taxid) > 1
    );

    UPDATE ff
    SET ff.[Dually Contracted] = 'Y'
    FROM #FirstResult ff
    WHERE EXISTS
    (
        SELECT 1
        FROM stg.facets_full f
        WHERE f.npi = ff.npi
              AND f.taxid = ff.tin_number
    ); -- also contracted by OHBS, may need to check other sources lates


    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;


    -- 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 ISNULL(f.remit_address1, '') = '' THEN
                                                        ', No BILL Address'
                                                    ELSE
                                                        ''
                                                END + CASE
                                                          WHEN ISNULL(f.loc_address1, '') = '' THEN
                                                              ', No PLSV Address'
                                                          ELSE
                                                              ''
                                                      END
                                          + CASE
                                                WHEN f.Request_Type = 'Add'
                                                     AND f.Provider_Type NOT IN ( 'Group', 'Solo', 'Rendering' ) THEN
                                                    ', No ProvType'
                                                ELSE
                                                    ''
                                            END + CASE
                                                      WHEN f.Request_Type IN ( 'Add', 'Change', 'Term' )
                                                           AND 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 + CASE
                                                            WHEN f.Request_Type IN ( 'Add', 'Change', 'Term' )
                                                                 AND COALESCE(npi, '') = '' THEN
                                                                ', No NPI'
                                                            ELSE
                                                                ''
                                                        END
                                      ELSE
                                          ''
                                  END AS VARCHAR(MAX))
    FROM #FirstResult f;


    ;WITH cteEffectiveDate
    AS (SELECT MAX([OHCS Eff Date]) AS dt,
               f.tin_number,
               npi
        FROM #FirstResult f
        GROUP BY f.tin_number,
                 f.npi
        UNION ALL
        SELECT MIN(ff.firstcontracteffectivedate),
               ff.taxid,
               ff.npi
        FROM stg.facets_full ff
        WHERE ff.primarycontractedrelationship = 'Clinician'
        GROUP BY taxid,
                 npi)
    UPDATE f
    SET [f].[Social Grouping Effective Date] =
        (
            SELECT MAX(dt)FROM cteEffectiveDate
        )
    FROM #FirstResult f
    WHERE Request_Type = 'Add'
          AND Provider_Type = 'Rendering';

    /*
    
 Group or Solo: Store latest term date from OHCS daily file or date NPI disappeared from OHBS daily file.  Join on TIN
 Rendering: Store latest term date from OHCS daily file or date NPI disappeared from OHBS daily file. Join on NPI. 
    
    */
    ;
    WITH latestTermDate
    AS (SELECT MAX(status_date) AS dt,
               tin_number
        FROM #FirstResult
        GROUP BY tin_number
        UNION ALL
        SELECT MAX(hn.load_date) AS dt,
               tin AS tin_number
        FROM rv.hub_tin hn
        WHERE NOT EXISTS
        (
            SELECT 1
            FROM stg.stg_facets_full f
            WHERE f.tin_hk = hn.tin_hk
                  AND f.primary_contracted_relationship = 'Clinician'
                  AND hn.source = 'FACETS'
        )
        GROUP BY hn.tin)
    UPDATE f
    SET [f].[Latest Term Date] = ltd.Maxdate
    FROM #FirstResult f
        JOIN
        (
            SELECT MAX(dt) AS Maxdate,
                   latestTermDate.tin_number
            FROM latestTermDate
            GROUP BY latestTermDate.tin_number
        ) AS ltd
            ON ltd.tin_number = f.tin_number
    WHERE Provider_Type IN ( 'Group', 'Solo' )
          AND [Status] = 'Termed';

    ;WITH latestTermDate1
    AS (SELECT MAX(status_date) AS dt,
               npi
        FROM #FirstResult
        GROUP BY npi
        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'
        )
              AND hn.source = 'FACETS'
        GROUP BY hn.npi)
    UPDATE f
    SET [f].[Latest Term Date] = ltd.Maxdate
    FROM #FirstResult f
        JOIN
        (
            SELECT MAX(dt) AS Maxdate,
                   latestTermDate1.npi
            FROM latestTermDate1
            GROUP BY latestTermDate1.npi
        ) AS ltd
            ON ltd.npi = f.npi
    WHERE Provider_Type IN ( 'Rendering' )
          AND [Status] = 'Termed';


    ;WITH latestTermDate2
    AS (SELECT MAX(status_date) AS dt,
               npi_hk,
               tin_hk
        FROM #FirstResult
        GROUP BY npi_hk,
                 tin_hk
        UNION ALL
        SELECT MAX(hn.load_date) AS dt,
               hn.npi_hk,
               hn.tin_hk
        FROM [rv].[view_lnk_npi_tin_view_stg_facets_npi_tin] 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'
        )
              AND hn.source = 'FACETS'
        GROUP BY hn.npi_hk,
                 hn.tin_hk)
    UPDATE f
    SET [f].[SG Latest Term Date] = cc.MaxDt
    FROM #FirstResult f
        JOIN
        (
            SELECT MAX(dt) AS MaxDt,
                   latestTermDate2.npi_hk,
                   tin_hk
            FROM latestTermDate2
            GROUP BY latestTermDate2.npi_hk,
                     tin_hk
        ) cc
            ON cc.npi_hk = f.npi_hk
               AND cc.tin_hk = f.tin_hk
    WHERE f.Provider_Type IN ( 'Rendering' )
          AND f.[Status] = 'Termed';




    -- Term Statuses

    /*
   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 correlating 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 correlating 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.
   
   "Need to add an indicator for a full or partial term request.
Full Term - the entire TIN is terming - or a rendering no longer has any active TINS
Partial Term - there may be mulitple TINS, and some may still be active while others need to be termed
**NOTE: DC/LAC/MT - considered social grouped specialties and do not need an active Group TIN"
   *
   GROUP	TIN contracted in OHBS	TIN NOT contracted in OHBS		Technicaal Requirement Notes
"Is TIN contracted in outside source? 

If Yes, follow Column B.
If No, follow Column C."	"Do not send a Term Request
"	"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
Specialties DC/LAC/MT will need to be sent to with exclusion reason ""Social Grouping Required"""		"Need to add an indicator for a full or partial term request.
Full Term - the entire TIN is terming - or a rendering no longer has any active TINS
Partial Term - there may be mulitple TINS, and some may still be active while others need to be termed
**NOTE: DC/LAC/MT - considered social grouped specialties and do not need an active Group TIN"
                
                
SOLO	TIN contracted in OHBS	TIN NOT contracted in OHBS		
"Is TIN contracted in outside source? 

If Yes, follow Column B.
If No, follow Column C."	"Do not send a Term Request
"	Send as FULL TERM - 		
                
                
RENDERING	"TIN contracted in OHBS
"	"NPI contracted for DIFFERENT TIN in OHBS OR
NPI contracted for SAME TIN in OHBS with Primary Contracted Relationship = Group"	NPI NOT contracted in OHBS	
NPI contracted for DIFFERENT TIN in OHCS	"Do not send a Term Request
"	"Send as a PARTIAL TERM Request:
"	Send as a PARTIAL TERM Request	
NPI NOT contracted in OHCS	"Do not send a Term Request
"	"Send as a PARTIAL TERM Request
"	Send as FULL TERM	
 */


    ;
    WITH cte1
    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]
              )
              AND 'Termed'=ALL
              (
                  SELECT Status
                  FROM #FirstResult f2
                  WHERE f.tin_number = f2.tin_number
                        AND Provider_Type = 'Rendering'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
                        AND f.specialty IN ( 'PT', 'OT', 'SLP' )
              ))


    /*

            "Need to add an indicator for a full or partial term request.
Full Term - the entire TIN is terming - or a rendering no longer has any active TINS
Partial Term - there may be mulitple TINS, and some may still be active while others need to be termed
**NOTE: DC/LAC/MT - considered social grouped specialties and do not need an active Group TIN""Need to add an indicator for a full or partial term request.

   )
   */

    UPDATE f
    SET f.Request_Type = 'Term'
    FROM #FirstResult f
        INNER JOIN cte1
            ON cte1.RN = 1
               AND f.tin_number = cte1.tin_number
               AND f.[OHCS Term Date] = cte1.[OHCS Term Date]
               AND NOT EXISTS
                       (
                           SELECT 1 FROM stg.facets_full f1 WHERE f1.taxid = f.tin_number
                       ); -- TIN not contracted by  OHBS

    ;
    WITH cte2
    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]
              )
              AND 'Active'=ANY
              (
                  (SELECT Status
                   FROM #FirstResult f2
                   WHERE f.tin_number = f2.tin_number
                         AND Provider_Type = 'Rendering'
                         AND [OHCS Term Date] >= [OHCS Eff Date]
                         AND f.specialty IN ( 'PT', 'OT', 'SLP' ))
              ))
    UPDATE f
    SET f.Request_Type = 'Partial Term'
    FROM #FirstResult f
        INNER JOIN cte2
            ON cte2.RN = 1
               AND f.tin_number = cte2.tin_number
               AND f.[OHCS Term Date] = cte2.[OHCS Term Date]
               AND NOT EXISTS
                       (
                           SELECT 1 FROM stg.facets_full f1 WHERE f1.taxid = f.tin_number
                       ); -- TIN not contracted by  OHBS





    ;
    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] --AND f.npi =f2.npi
              )
              AND 'Active'=ANY
              (
                  SELECT Status
                  FROM #FirstResult f2
                  WHERE f.tin_number = f2.tin_number
                        AND Provider_Type = 'Rendering'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
                        AND f2.specialty IN ( 'DC', 'LAC', 'MT' )
              ))
    UPDATE f
    SET f.Request_Type = '',
        f.Exclusion_Reason = COALESCE(NULLIF(f.Exclusion_Reason, '') + ', ', '') + 'Social Grouping Required'
    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
                       );



    -- Solo - TIN contracted in OHBS - do not send a term request
    ;
    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
                       ) -- TIN not contracted in OHBS;

    -- Rendering
    ;
    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 Provider_Type = 'Rendering'
                        AND [OHCS Term Date] >= [OHCS Eff Date]
              ))
    UPDATE f
    SET f.Request_Type = CASE
                             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 = 'Group'
                                     ) 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
                                      ) --NPI not contracted in OHBS
                                  AND NOT EXISTS
                                          (
                                              SELECT 1 FROM #FirstResult f2 WHERE f2.npi = f.npi
                                          ) -- NPI not contracted in OHCS

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

    WITH cteEarliestEffectiveDate (dt, tin_number)
    AS (SELECT MIN([OHCS Eff Date]) AS dt,
               f.tin_number
        FROM #FirstResult f
        GROUP BY f.tin_number
        UNION ALL
        SELECT MIN(f.firstcontracteffectivedate) AS dt,
               f.taxid
        FROM stg.facets_full f
        GROUP BY npi,
                 f.taxid)
    UPDATE f
    SET [f].[Earliest Eff Date] = MinDt
    FROM #FirstResult f
        JOIN
        (
            SELECT MIN(dt) AS MinDt,
                   tin_number
            FROM cteEarliestEffectiveDate
            GROUP BY tin_number
        ) cc
            ON cc.tin_number = f.tin_number
    WHERE f.Provider_Type IN ( 'Group', 'Solo' );


    WITH cteEarliestEffectiveDate (dt, npi)
    AS (SELECT MIN([OHCS Eff Date]) AS dt,
               npi
        FROM #FirstResult f
        GROUP BY f.npi
        UNION ALL
        SELECT MIN(f.firstcontracteffectivedate) AS dt,
               f.npi
        FROM stg.facets_full f
        GROUP BY npi)
    UPDATE f
    SET [f].[Earliest Eff Date] = MinDt
    FROM #FirstResult f
        JOIN
        (
            SELECT MIN(dt) AS MinDt,
                   npi
            FROM cteEarliestEffectiveDate
            GROUP BY npi
        ) cc
            ON cc.npi = f.npi
    WHERE f.Provider_Type = 'Rendering';




    SET @message = 'Updates done 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);
    CREATE NONCLUSTERED INDEX idxTIn ON #FirstResult (tin_number);
    CREATE NONCLUSTERED INDEX idxTInNpi ON #FirstResult (tin_number, npi);


    SELECT 'Automation OHCS' AS [Source],
           'Professional' AS Category,
           cte.Provider_Type AS ProvType,
           CAST(cte.Social_Group AS VARCHAR(3)) AS [Social Group],
           [Social Group Partner],
           IIF(Provider_Type IN ( 'GROUP', 'SOLO' ),
               'P' + npi, -- + MAID, 
               'P' + npi) AS [Tracer ID],
           COALESCE(Request_Type, '') AS [Request Type],
           [Change_Type] AS [Change Type],
           CAST(cte.[Status] AS VARCHAR(10)) AS [Status],
           CAST(cte.[OHCS Eff Date] AS DATE) AS [OHCS Eff Date],
           CAST(cte.[Earliest Eff Date] AS DATE) AS [Earliest Eff Date],
           cte.[OHCS Change Date],
           CASE
               WHEN cte.[Status] = 'Thermed' THEN
                   CAST(cte.[OHCS Term Date] AS DATE)
               ELSE
                   NULL
           END AS [OHCS Term Date],
           CASE
               WHEN cte.[Status] = 'Thermed' THEN
                   [cte].[Latest Term Date]
           END AS [Latest Term Date],
           cte.[SG Latest Term Date],
           cte.Exclusion_Reason AS ExclusionReason,
           cte.tin_number AS TinNumber,
           cte.npi AS NPI,
           CAST(cte.provider_id AS VARCHAR(50)) AS ProviderID,
           CASE
               WHEN cte.Provider_Type = 'GROUP' THEN
                   CAST(cte.clinic_Name AS VARCHAR(39))
               ELSE
                   CAST(cte.Prov_Last_Name AS VARCHAR(18))
           END AS ProviderLastName,
           CAST(cte.prov_first_name AS VARCHAR(30)) AS providerFirstName,
           CAST(cte.prov_mi AS VARCHAR(30)) AS ProviderMI,
           CAST(cte.clinic_Name AS VARCHAR(39)) AS ClinicName,
           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 ProvType],
           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 ( 'Rendering', 'Solo' ) THEN
                        MIN(cte.license_number) OVER ()
                    ELSE
                        ''
                END AS VARCHAR(15)) AS LicenseNumber,
           CASE
               WHEN cte.Provider_Type IN ( 'Rendering', '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 LicenseState,
           CASE
               WHEN cte.Provider_Type IN ( 'Rendering', '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
                   NULL
           END AS LicenseIssueDate,
           CAST(cte.loc_address1 AS VARCHAR(30)) AS LocAddress1,
           CAST(cte.loc_address2 AS VARCHAR(30)) AS LocAddress22,
           CAST(cte.loc_city AS VARCHAR(30)) AS LocCity,
           CAST(cte.loc_state AS VARCHAR(30)) AS LocState,
           CAST(cte.loc_zip AS VARCHAR(9)) AS LocZip,
           CAST(cte.remit_address1 AS VARCHAR(50)) AS RemitAddress1,
           CAST(cte.remit_address2 AS VARCHAR(50)) AS RemitAdress2,
           CAST(cte.remit_city AS VARCHAR(30)) AS RemitCity,
           CAST(cte.remit_state AS VARCHAR(2)) AS RemitState,
           CAST(cte.remit_zip AS VARCHAR(9)) AS RemitZip,
           cte.prov_group AS ProvGroup,
           FIRST_VALUE(cte.specialty) OVER (ORDER BY cte.specialty) AS Specialty,
           main_status AS MainStatus,
           sub_status AS SubStatus,
           status_date AS StatusDate,
           [OHCS Eff Date] AS [Client Eff Date],
           sub_specialty AS SubSpecialty,
           loc_county AS LocCounty,
           acn_prov_id AS AcnProvID,
           office_location_id AS OfficeLocationID,
           loc_eff_date AS LocEffDate,
           loc_term_date AS LocTermDate,
           tin_eff_date AS TinEffDate,
           tin_term_date AS TinTermDate,
           prov_phone AS ProvPhone,
           prov_fax AS ProvFax,
           gender,
           client_term_date AS ClientTermDate,
           cred_status_eff_date AS CredStatusEffDate,
           [Social Grouping Effective Date],
           cte.[Dually Contracted]
    FROM #FirstResult cte
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte.specialty = tx.specialty;
    --	(USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

    -- OPTION  OPTION (FAST 10); (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

    SET STATISTICS TIME OFF;
    SET STATISTICS IO OFF;

END;

---------------------------------------------------------------------------------------------------
EXECUTE im_automation.accent_changes
WITH RESULT SETS
(
    (
        [Source] VARCHAR(30),
        Category VARCHAR(30),
        provtype VARCHAR(30),
        [Social Group] VARCHAR(3),
        [Social Group Partner] VARCHAR(30),
        [Tracer ID] VARCHAR(30),
        [Request Type] VARCHAR(20),
        [Change Type] VARCHAR(100),
        [Status] VARCHAR(10),
        [OHCS Eff Date] DATE,
        [Earliest Eff Date] DATE,
        [OHCS Change Date] DATE,
        [OHCS Term Date] DATE,
        [Latest Term Date] DATE,
        [SG Latest Term Date] DATE,
        ExclusionRreason VARCHAR(MAX),
        tinnumber VARCHAR(20),
        NPI VARCHAR(10),
        ProviderId VARCHAR(30),
        provlastname VARCHAR(40),
        provfirstname VARCHAR(40),
        provmi VARCHAR(10),
        clinicname VARCHAR(40),
        [T50 Specialty] VARCHAR(100),
        [T50 ProvType] VARCHAR(100),
        [T15 Taxonomy] VARCHAR(100),
        licensenumber VARCHAR(40),
        LicenseSate VARCHAR(10),
        licenseissuedate DATE,
        locaddress1 VARCHAR(100),
        locaddress2 VARCHAR(100),
        loccity VARCHAR(100),
        locstate VARCHAR(10),
        loczip VARCHAR(10),
        remitaddress1 VARCHAR(100),
        remitaddress2 VARCHAR(100),
        remitcity VARCHAR(100),
        remitstate VARCHAR(10),
        remitzip VARCHAR(10),
        provgroup VARCHAR(30),
        SPECIALTY VARCHAR(100),
        mainstatus VARCHAR(20),
        substatus VARCHAR(20),
        statusdate DATE,
        [Client Eff Date] DATE,
        subspecialty VARCHAR(100),
        loccounty VARCHAR(30),
        acnprovid VARCHAR(40),
        officelocationid VARCHAR(40),
        loceffdate DATE,
        loctermdate DATE,
        tineffdate DATE,
        tintermdate DATE,
        provphone VARCHAR(20),
        provfax VARCHAR(20),
        gender VARCHAR(30),
        ClienTermDate DATE,
        credstatuseffdate DATE,
        [Social Grouping Effective Date] DATE,
        [Dually Contracted] VARCHAR(3)
    )
);