VW_ROX_Avamere_Ulti_Mapping

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ROXFacName

varchar

null

ExportKey

varchar

null

AvaFacCode

varchar

null

UltiProjectCode

varchar

√

null

View Definition

CREATE VIEW dbo.VW_ROX_Avamere_Ulti_Mapping AS

/*********************************************************************
Database:		DataCStore
View:			dbo.VW_ROX_Avamere_Ulti_Mapping
Created:		8/20/2020
Description:	Initially created for data exports to Kashi labs.
                May have possible future more general use.
                Infinity sites owned by Avamere, and their mappings.
*********************************************************************/

    WITH facs AS
             (SELECT 'Avamere at Three Fountains' AS ROXFacName,
                     '219'                        AS ROXExportKey,
                     '263'                        AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Court at Keizer' AS ROXFacName,
                     '16'                      AS ROXExportKey,
                     '226'                     AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Crestview of Portland' AS ROXFacName,
                     '217'                           AS ROXExportKey,
                     '016'                           AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Beaverton' AS ROXFacName,
                     '100'                        AS ROXExportKey,
                     '017'                        AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Clackamas' AS ROXFacName,
                     '5'                          AS ROXExportKey,
                     '005'                        AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Coos Bay' AS ROXFacName,
                     '11'                        AS ROXExportKey,
                     '004'                       AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Eugene' AS ROXFacName,
                     '7'                       AS ROXExportKey,
                     '001'                     AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Hillsboro' AS ROXFacName,
                     '13'                         AS ROXExportKey,
                     '333'                        AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Junction City' AS ROXFacName,
                     '10'                             AS ROXExportKey,
                     '006'                            AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of King City' AS ROXFacName,
                     '17'                         AS ROXExportKey,
                     '010'                        AS AvaFacCode
              UNION ALL
              SELECT 'Richmond Beach' AS ROXFacName,
                     '51'             AS ROXExportKey,
                     '120'            AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Lebanon' AS ROXFacName,
                     '40'                       AS ROXExportKey,
                     '008'                      AS AvaFacCode
              UNION ALL

              SELECT 'Avamere Rehab of Newport' AS ROXFacName,
                     '24'                       AS ROXExportKey,
                     '015'                      AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Oregon City' AS ROXFacName,
                     '21'                           AS ROXExportKey,
                     '009'                          AS AvaFacCode
              UNION ALL
              SELECT 'Salem Transitional Care' AS ROXFacName,
                     '34'                      AS ROXExportKey,
                     '003'                     AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Riverpark of Eugene' AS ROXFacName,
                     '218'                         AS ROXExportKey,
                     '001'                         AS AvaFacCode
              UNION ALL
              SELECT 'Avamere St Francis of Bellingham' AS ROXFacName,
                     '220'                              AS ROXExportKey,
                     '164'                              AS AvaFacCode
              UNION ALL
              SELECT 'Avamere at Pacific Ridge' AS ROXFacName,
                     '36'                       AS ROXExportKey,
                     '154'                      AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Olympic Rehab of Sequim' AS ROXFacName,
                     '74'                              AS ROXExportKey,
                     '119'                             AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Heritage Rehab of Tacoma' AS ROXFacName,
                     '12'                               AS ROXExportKey,
                     '152'                              AS AvaFacCode
              UNION ALL
              SELECT 'Bend Transitional Care' AS ROXFacName,
                     '271'                    AS ROXExportKey,
                     '275'                    AS AvaFacCode
              UNION ALL
              SELECT 'The Pearl at Kruse Way' AS ROXFacName,
                     '203'                    AS ROXExportKey,
                     '227'                    AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Hlth Svcs Rogue Valley' AS ROXFacName,
                     '388'                            AS ROXExportKey,
                     '071'                            AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Bellingham HC & RHB Services' AS ROXFacName,
                     '390'                                  AS ROXExportKey,
                     '155'                                  AS AvaFacCode
              UNION ALL
              SELECT 'Queen Anne Healthcare' AS ROXFacName,
                     '389'                   AS ROXExportKey,
                     '156'                   AS AvaFacCode
              UNION ALL
              SELECT 'Avamere TCR - Malley' AS ROXFacName,
                     '393'                  AS ROXExportKey,
                     '171'                  AS AvaFacCode
              UNION ALL
              SELECT 'Avamere TCR - Brighton' AS ROXFacName,
                     '392'                    AS ROXExportKey,
                     '172'                    AS AvaFacCode
              UNION ALL
              SELECT 'Avamere TCR - Boise' AS ROXFacName,
                     '391'                 AS ROXExportKey,
                     '170'                 AS AvaFacCode
              UNION ALL
              SELECT 'Laurelhurst Village Rehab Ctr' AS ROXFacName,
                     '574'                           AS ROXExportKey,
                     '710'                           AS AvaFacCode
              UNION ALL
              SELECT 'Avamere TC of Sunnyside' AS ROXFacName,
                     '412'                     AS ROXExportKey,
                     '002'                     AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehab of Cascade Park' AS ROXFacName,
                     '475'                           AS ROXExportKey,
                     '112'                           AS AvaFacCode
              UNION ALL
              SELECT 'Avamere TC of Puget Sound' AS ROXFacName,
                     '523'                       AS ROXExportKey,
                     '157'                       AS AvaFacCode
              UNION ALL
              SELECT 'Avamere Rehabilitation at Fiesta Park' AS ROXFacName,
                     '592'                                   AS ROXExportKey,
                     '173'                                   AS AvaFacCode)

    SELECT rf.FacName AS ROXFacName,
           rf.ExportKey,
           facs.AvaFacCode,
           RIGHT('00' + rf.ExportKey, 3) AS UltiProjectCode
    FROM dbo.ROX_Cache_Fac AS rf
    JOIN facs
    ON rf.ExportKey = facs.ROXExportKey
    WHERE rf.IsInactive = 0

Columns

View Definition

Possibly Referenced Tables/Views