CREDIT

Fields

Field	Field Type	Null
ID	uniqueidentifier	yes
SALESORDERID	uniqueidentifier	yes
AMOUNT	money	yes
ADDEDBYID	uniqueidentifier
CHANGEDBYID	uniqueidentifier
DATEADDED	datetime
DATECHANGED	datetime
TS	timestamp	yes
TSLONG	bigint	yes
CREDITREASONCODEID	uniqueidentifier	yes
COMMENT	nvarchar(700)
TRANSACTIONDATE	datetime	yes
APPUSERID	uniqueidentifier	yes
TYPECODE	int
TYPE	nvarchar(27)	yes
DISCOUNTID	uniqueidentifier	yes
CONSTITUENTID	uniqueidentifier	yes
TRANSACTIONDATEWITHTIMEOFFSET	datetimeoffset	yes
MEMBERSHIPPROMOID	uniqueidentifier	yes
SALESORDERITEMMEMBERSHIPITEMPROMOTIONID	uniqueidentifier	yes

Definition

Copy


CREATE view dbo.CREDIT with schemabinding as
    select FT.ID
        ,isnull(SO.ID, EXT.SALESORDERID) [SALESORDERID]
        ,FT.TRANSACTIONAMOUNT as AMOUNT
        ,FT.ADDEDBYID
        ,FT.CHANGEDBYID
        ,FT.DATEADDED
        ,FT.DATECHANGED
        ,FT.TS
        ,FT.TSLONG
        ,EXT.CREDITREASONCODEID
        ,FT.[DESCRIPTION] [COMMENT]
        ,CAST(FT.[DATE] as datetime) [TRANSACTIONDATE]
        ,FT.APPUSERID
        ,0 as [TYPECODE] --Refund
        ,FT.[TYPE]
        ,null as [DISCOUNTID]
        ,FT.CONSTITUENTID
        ,FT.[DATE] [TRANSACTIONDATEWITHTIMEOFFSET]
        ,null as [MEMBERSHIPPROMOID]
        ,null as [SALESORDERITEMMEMBERSHIPITEMPROMOTIONID]
    from dbo.FINANCIALTRANSACTION FT
    inner join dbo.CREDIT_EXT EXT on FT.ID = EXT.ID
    left join dbo.SALESORDER SO on SO.REVENUEID = FT.PARENTID

    union all

select EXT.CREDITID [ID]
        ,isnull(SO.ID, EXT.SALESORDERID) [SALESORDERID]
        ,SUM(LI.TRANSACTIONAMOUNT) [AMOUNT]
        ,LI.ADDEDBYID
        ,LI.CHANGEDBYID
        ,LI.DATEADDED
        ,LI.DATECHANGED
        ,MAX(LI.TS)
        ,MAX(LI.TSLONG)
        ,null [CREDITREASONCODEID]
        ,'' [COMMENT]
        ,null [TRANSACTIONDATE]
        ,null [APPUSERID]
        ,1 as [TYPECODE] --Discount
        ,LI.[TYPE]
        ,EXT.DISCOUNTID
        ,null as [CONSTITUENTID]
        ,null as [TRANSACTIONDATEWITHTIMEOFFSET]
        ,EXT.MEMBERSHIPPROMOID
        ,EXT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
    from dbo.FINANCIALTRANSACTIONLINEITEM LI
    inner join dbo.CREDITITEM_EXT EXT on LI.ID = EXT.ID
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
    left join dbo.SALESORDER SO on SO.REVENUEID = FT.ID
    where LI.TYPECODE = 5 and FT.TYPECODE in (5, 99) --Discount
    group by EXT.DISCOUNTID
        ,EXT.MEMBERSHIPPROMOID
        ,EXT.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
        ,EXT.CREDITID
        ,SO.ID
        ,LI.ADDEDBYID
        ,LI.CHANGEDBYID
        ,LI.DATEADDED
        ,LI.DATECHANGED
        ,LI.[TYPE]
        ,EXT.SALESORDERID