VW_HCHB_ar_monthend_revenue

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

closing_period

date

√

null

reporting_branchcode

char

√

null

branch_financename

varchar

200

√

null

branch_groupname

varchar

200

null

Ref_Signature_BranchGroup.branch_groupname

Implied ConstraintR

branch_serviceline

varchar

200

√

null

keyid

int

√

null

keydate

date

√

null

payor_type

varchar

√

null

payor_source

varchar

500

√

null

lastname

varchar

√

null

firstname

varchar

√

null

type

varchar

null

reporting_period

date

√

null

earned_revenue

decimal

17,4

√

null

manual_adj

decimal

17,4

√

null

sys_adj

decimal

17,4

√

null

unearned_revenue

decimal

17,4

√

null

cash

decimal

17,4

√

null

current_period_flag

varchar

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW dbo.VW_HCHB_ar_monthend_revenue AS

/*********************************************************************
  Signature AR month end revenue data from HCHB, for use in the enterprise
  AR project.

  git repo: entar.
*********************************************************************/

    SELECT CONVERT(DATE, LEFT(rev.closing_period, 4) + '-' + RIGHT(rev.closing_period, 2) + '-01') AS closing_period,
           rev.reporting_branchcode,
           br.branch_financename,
           ISNULL(br.branch_groupname, 'Unknown') AS branch_groupname,
           br.branch_serviceline,
           rev.keyid,
           CONVERT(DATE, rev.keydate) AS keydate,
           rev.payor_type,
           rev.payor_source,
           rev.lastname,
           rev.firstname,
           /* 0 = nonpps, 1 = pps, 2 = PDGM. Per Todd H, nonpps is same as ffs (fee for service) */
           CASE ISNULL(rev.PPS, -1) WHEN 0 THEN 'FFS' WHEN 1 THEN 'PPS' WHEN 2 THEN 'PDGM' ELSE 'Unknown' END AS type,
           CONVERT(DATE, rev.reporting_period_datetime) AS reporting_period,
           rev.earned_revenue,
           rev.manualAdj AS manual_adj,
           rev.sysAdj AS sys_adj,
           rev.unearned_revenue,
           rev.cash,
           CASE WHEN rev.closing_period = ISNULL(curr_month.hcpb_period, 0) THEN 'Y' ELSE 'N' END AS current_period_flag
    FROM dbo.HCHB_MonthEndCloseRev AS rev
    LEFT JOIN dbo.Ref_Signature_Branch AS br
        ON rev.reporting_branchcode = br.branch_code
    LEFT JOIN dbo.HCHB_MonthEnd_CurrMonth AS curr_month
        ON rev.closing_period = curr_month.hcpb_period

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views