PROGRAM_NET_REVENUE

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

PROGRAM_ID

int

4

√

null

The program ID

CUSTOMER_ID

int

4

√

null

Identified one customer by id

PROGRAM_NAME

varchar

255

√

null

The program Name

NET_REVENUE

nvarchar

8000

√

null

The net revenue of this program

LAST_SYNC_TIME

datetime

16,3

√

null

Date and time Hub record was last modified

ORG_ID

int

4

√

null

Your organization’s unique identifier in the Active Net system

source_id

int

4

√

null

The ID value from the Active Net system

Relationships

Close relationships within degrees of separation

One

View Definition

CREATE VIEW dbo.PROGRAM_NET_REVENUE 
AS 
SELECT  
      src.PROGRAM_ID,
       SRC.CUSTOMER_ID,
	   SRC.PROGRAM_NAME,
       SRC.NET_REVENUE,
	   SRC.LAST_SYNC_TIME,
       --src.ACTIVE_HUB_PROCESS_ID,
	   SRC.ORG_ID,
	   SRC.PROGRAM_ID as source_id
FROM
(
    SELECT 
		  P.PROGRAM_ID,
           T.CUSTOMER_ID,
           P.PROGRAM_NAME,
           T.ORG_ID,
           FORMAT(SUM(   CASE
                             WHEN GLA.GLACCOUNT_ID = 5 THEN
                                 -(GL.AMOUNT)
                             ELSE
                                 0.00
                         END
                     ) + SUM(   CASE
                                    WHEN GLA.SYSTEM_ACCOUNT = 0 THEN
                                        -(GL.AMOUNT)
                                    ELSE
                                        0.00
                                END
                            ),
                  'N',
                  'EN-US'
                 ) AS NET_REVENUE,
           MAX(RD.ACTIVE_HUB_PROCESS_ID) AS ACTIVE_HUB_PROCESS_ID,
           MAX(RD.LAST_SYNC_TIME) AS LAST_SYNC_TIME
FROM HUB.TRANSACTIONS T
    INNER JOIN hub.CUSTOMERS c
        ON T.CUSTOMER_ID = c.CUSTOMER_ID
           AND T.ORG_ID = c.ORG_ID
    INNER JOIN HUB.RECEIPT_DETAILS RD
        ON RD.TRANSACTION_ID = T.TRANSACTION_ID
           AND T.ORG_ID = RD.ORG_ID
    INNER JOIN HUB.RECEIPT_GL_POSTINGS GL
        ON GL.RECEIPT_DETAIL_ID = RD.RECEIPT_DETAIL_ID
           AND GL.ORG_ID = RD.ORG_ID
    INNER JOIN HUB.GLACCOUNTS GLA
        ON GLA.GLACCOUNT_ID = GL.GLACCOUNT_ID
           AND GLA.ORG_ID = GL.ORG_ID
    INNER JOIN HUB.PROGRAMS P
        ON P.PROGRAM_ID = T.PROGRAM_ID
           AND P.ORG_ID = T.ORG_ID
WHERE T.VOIDED = 0
      AND T.PROGRAM_ID > 0
      AND RD.VOIDED = 0
      AND GL.VOIDED = 0
GROUP BY P.PROGRAM_ID,
         T.CUSTOMER_ID,
         P.PROGRAM_NAME,
         T.ORG_ID
) SRC;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views