REVENUEBENEFITAUDIT

CREATE view [dbo].[REVENUEBENEFITAUDIT] as 
        WITH Audit_CTE 
    as (
        select T.AUDITID
            ,T.AUDITRECORDID
            ,T.AUDITKEY
            ,T.AUDITTYPECODE
            ,T.FT_COUNT
            ,T.RE_COUNT
            ,DENSE_RANK() over (PARTITION by T.AUDITRECORDID order by T.RE_RECORDID asc) [RANK]
            ,T.AUDITCHANGEAGENTID
            ,T.AUDITDATE
            ,T.AUDITTYPE
            ,T.REVENUEID
            ,T.BENEFITID
            ,T.QUANTITY
            ,T.UNITVALUE
            ,T.DETAILS
            ,T.SEQUENCE
            ,T.ADDEDBYID
            ,T.CHANGEDBYID
            ,T.DATEADDED
            ,T.DATECHANGED
            ,T.SENDBENEFIT
            ,T.PERCENTAPPLICABLEAMOUNT
            ,T.VALUEPERCENT
            ,T.TRANSACTIONTOTALVALUE
            ,T.ORGANIZATIONTOTALVALUE
            ,T.BASECURRENCYID
            ,T.TRANSACTIONCURRENCYID
            ,T.BASEEXCHANGERATEID
            ,T.ORGANIZATIONEXCHANGERATEID
            ,T.REVENUESPLITID
        from (
            select FTLI.AUDITID
                ,null [RE_RECORDID]
                ,FTLI.AUDITRECORDID
                ,FTLI.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by FTLI.AUDITRECORDID order by FTLI.AUDITDATE, FTLI.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by FTLI.AUDITRECORDID order by FTLI.AUDITDATE, FTLI.AUDITTYPECODE desc) [RE_COUNT]
                ,FTLI.AUDITKEY
                ,FTLI.AUDITCHANGEAGENTID
                ,FTLI.AUDITDATE
                ,FTLI.AUDITTYPE
                ,FTLI.FINANCIALTRANSACTIONID as REVENUEID
                ,null as BENEFITID
                ,FTLI.QUANTITY
                ,FTLI.UNITVALUE
                ,FTLI.DESCRIPTION as DETAILS
                ,FTLI.SEQUENCE
                ,FTLI.ADDEDBYID
                ,FTLI.CHANGEDBYID
                ,FTLI.DATEADDED
                ,FTLI.DATECHANGED
                ,null as SENDBENEFIT
                ,null as PERCENTAPPLICABLEAMOUNT
                ,null as VALUEPERCENT
                ,FTLI.TRANSACTIONAMOUNT as TRANSACTIONTOTALVALUE
                ,FTLI.ORGAMOUNT as ORGANIZATIONTOTALVALUE
                ,NULL as BASECURRENCYID-- Currency exchange rates are now in the parent table, so we won't do the history here.
                ,NULL as TRANSACTIONCURRENCYID
                ,NULL as BASEEXCHANGERATEID
                ,NULL as ORGANIZATIONEXCHANGERATEID
                ,null as REVENUESPLITID
            from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT FTLI
            where FTLI.TYPECODE = 3

            union all

            select RE.AUDITID
                ,RE.AUDITRECORDID [RE_RECORDID]
                ,RE.AUDITRECORDID
                ,RE.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by RE.AUDITRECORDID order by RE.AUDITDATE, RE.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by RE.AUDITRECORDID order by RE.AUDITDATE, RE.AUDITTYPECODE desc) [RE_COUNT]
                ,RE.AUDITKEY
                ,RE.AUDITCHANGEAGENTID
                ,RE.AUDITDATE
                ,RE.AUDITTYPE
                ,null as REVENUEID
                ,RE.BENEFITID
                ,null as QUANTITY
                ,null as UNITVALUE
                ,null as DETAILS
                ,null as SEQUENCE
                ,RE.ADDEDBYID
                ,RE.CHANGEDBYID
                ,RE.DATEADDED
                ,RE.DATECHANGED
                ,RE.SENDBENEFIT
                ,RE.PERCENTAPPLICABLEAMOUNT
                ,RE.VALUEPERCENT
                ,null as TRANSACTIONTOTALVALUE
                ,null as ORGANIZATIONTOTALVALUE
                ,CS.BASECURRENCYID
                ,null as TRANSACTIONCURRENCYID
                ,null as BASEEXCHANGERATEID
                ,null as ORGANIZATIONEXCHANGERATEID
                ,RE.REVENUESPLITID
            from dbo.REVENUEBENEFIT_EXTAUDIT RE
            left join dbo.FINANCIALTRANSACTIONLINEITEM on RE.AUDITRECORDID = FINANCIALTRANSACTIONLINEITEM.ID
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            left join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
            left join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
        ) T
    )
    select CTE1.AUDITID
        ,CTE1.AUDITRECORDID
        ,CTE1.AUDITKEY
        ,CTE1.AUDITCHANGEAGENTID
        ,CTE1.AUDITDATE
        ,CTE1.AUDITTYPECODE
        ,CTE1.AUDITTYPE
        ,coalesce(CTE1.REVENUEID, CTE2.REVENUEID, CASE WHEN CTE2.AUDITID is null THEN FT.FINANCIALTRANSACTIONID ELSE NULL END) REVENUEID
        ,coalesce(CTE1.BENEFITID, CTE2.BENEFITID, CASE WHEN CTE2.AUDITID is null THEN RE.BENEFITID ELSE NULL END) BENEFITID
        ,cast(coalesce(CTE1.QUANTITY, CTE2.QUANTITY, CASE WHEN CTE2.AUDITID is null THEN FT.QUANTITY ELSE null end) as int) QUANTITY
        ,coalesce(CTE1.UNITVALUE, CTE2.UNITVALUE, CASE WHEN CTE2.AUDITID is null THEN FT.UNITVALUE ELSE null end) UNITVALUE
        ,coalesce(CTE1.DETAILS, CTE2.DETAILS, CASE WHEN CTE2.AUDITID is null THEN FT.DESCRIPTION ELSE NULL END) DETAILS
        ,coalesce(CTE1.SEQUENCE, CTE2.SEQUENCE, CASE WHEN CTE2.AUDITID is null THEN FT.SEQUENCE ELSE NULL END) SEQUENCE
        ,CTE1.ADDEDBYID
        ,CTE1.CHANGEDBYID
        ,CTE1.DATEADDED
        ,CTE1.DATECHANGED
        ,coalesce(CTE1.SENDBENEFIT, CTE2.SENDBENEFIT, CASE WHEN CTE2.AUDITID is null THEN RE.SENDBENEFIT ELSE NULL END) SENDBENEFIT
        ,coalesce(CTE1.PERCENTAPPLICABLEAMOUNT, CTE2.PERCENTAPPLICABLEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN RE.PERCENTAPPLICABLEAMOUNT ELSE NULL END) PERCENTAPPLICABLEAMOUNT
        ,coalesce(CTE1.VALUEPERCENT, CTE2.VALUEPERCENT, CASE WHEN CTE2.AUDITID is null THEN RE.VALUEPERCENT ELSE NULL END) VALUEPERCENT
        ,coalesce(CTE1.TRANSACTIONTOTALVALUE, CTE2.TRANSACTIONTOTALVALUE, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONAMOUNT ELSE NULL END) TRANSACTIONTOTALVALUE
        ,coalesce(CTE1.ORGANIZATIONTOTALVALUE, CTE2.ORGANIZATIONTOTALVALUE, CASE WHEN CTE2.AUDITID is null THEN FT.ORGAMOUNT ELSE NULL END) ORGANIZATIONTOTALVALUE
        ,coalesce(CTE1.BASECURRENCYID, CTE2.BASECURRENCYID) BASECURRENCYID
        ,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID) TRANSACTIONCURRENCYID
        ,coalesce(CTE1.BASEEXCHANGERATEID, CTE2.BASEEXCHANGERATEID) BASEEXCHANGERATEID
        ,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID) ORGANIZATIONEXCHANGERATEID
        ,coalesce(CTE1.REVENUESPLITID, CTE2.REVENUESPLITID, CASE WHEN CTE2.AUDITID is null THEN RE.REVENUESPLITID ELSE NULL END) REVENUESPLITID
    from Audit_CTE [CTE1]
    left join Audit_CTE [CTE2] on (CTE1.AUDITRECORDID = CTE2.AUDITRECORDID) and (CTE1.AUDITTYPECODE != 2 and (((CTE1.[RANK] - 1 = CTE2.[RANK]) and (CTE2.FT_COUNT = 1)) or ((CTE1.[RANK] + 1 = CTE2.[RANK]) and (CTE2.RE_COUNT = 2))) or (CTE1.AUDITTYPECODE = 2 and CTE2.AUDITTYPECODE = 2 and CTE1.AUDITID != CTE2.AUDITID))
    left join dbo.FINANCIALTRANSACTIONLINEITEM FT on FT.ID = CTE1.AUDITRECORDID    
    left join dbo.REVENUEBENEFIT_EXT RE on RE.ID = CTE1.AUDITRECORDID;