REVENUESPLITAUDIT

CREATE view dbo.REVENUESPLITAUDIT as 
    WITH Audit_CTE 
    as (
        select 
          -- Audit booking
          T.AUDITID
      ,T.AUDITRECORDID
      ,T.AUDITKEY
      ,T.AUDITCHANGEAGENTID
      ,T.AUDITDATE
      ,T.AUDITTYPECODE
      ,T.AUDITTYPE
      -- Audited Columns
      ,T.REVENUEID
      ,T.DESIGNATIONID
      ,T.AMOUNT
      ,T.ADDEDBYID
      ,T.CHANGEDBYID
      ,T.DATEADDED
      ,T.DATECHANGED
      ,T.TYPECODE
      ,T.APPLICATIONCODE
      ,T.BASECURRENCYID
      ,T.ORGANIZATIONAMOUNT
      ,T.ORGANIZATIONEXCHANGERATEID
      ,T.TRANSACTIONAMOUNT
      ,T.TRANSACTIONCURRENCYID
      ,T.BASEEXCHANGERATEID
      ,T.OVERRIDEBUSINESSUNITS
      ,T.REVENUESPLITBUSINESSUNITOVERRIDECODEID
            -- Counters
            ,T.FT_COUNT
            ,T.RE_COUNT
            ,DENSE_RANK() over (PARTITION by T.AUDITRECORDID order by T.RE_RECORDID asc) [RANK]
        from (
            select -- history from FINANCIALLINEITEM audit tables
              -- Audit booking
              FTLIA.AUDITID
                ,null [RE_RECORDID]
                ,FTLIA.AUDITRECORDID
                ,FTLIA.AUDITTYPECODE
                ,FTLIA.AUDITKEY
                ,FTLIA.AUDITCHANGEAGENTID
                ,FTLIA.AUDITDATE
                ,FTLIA.AUDITTYPE
                -- Audited Columns
                ,FTLIA.FINANCIALTRANSACTIONID [REVENUEID]
        ,null [DESIGNATIONID] -- Comes from the EXT history
        ,FTLIA.TRANSACTIONAMOUNT as AMOUNT
        ,FTLIA.ADDEDBYID
        ,FTLIA.CHANGEDBYID
        ,FTLIA.DATEADDED
        ,FTLIA.DATECHANGED
        ,null [TYPECODE]
        ,null [APPLICATIONCODE] -- Comes from the EXT history
        ,CS.BASECURRENCYID
        ,FTLIA.ORGAMOUNT [ORGANIZATIONAMOUNT]
        ,FT.ORGEXCHANGERATEID [ORGANIZATIONEXCHANGERATEID]
        ,FTLIA.BASEAMOUNT [TRANSACTIONAMOUNT]
        ,FT.TRANSACTIONCURRENCYID
        ,FT.BASEEXCHANGERATEID
        ,null [OVERRIDEBUSINESSUNITS]
        ,null [REVENUESPLITBUSINESSUNITOVERRIDECODEID]
                --
                ,ROW_NUMBER() over (PARTITION by FTLIA.AUDITRECORDID order by FTLIA.AUDITDATE, FTLIA.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by FTLIA.AUDITRECORDID order by FTLIA.AUDITDATE, FTLIA.AUDITTYPECODE desc) [RE_COUNT]

            from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT FTLIA
            left outer join dbo.FINANCIALTRANSACTION as FT on FTLIA.FINANCIALTRANSACTIONID = FT.ID
            left outer join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
            left outer join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
        where FTLIA.TYPECODE = 0    --these should always have entries
            -- If there is an entry in the extension table it would show up in the RevenueSplit view so get these too.
            --or exists (select 1 from dbo.REVENUESPLIT_EXTAUDIT where AUDITRECORDID = FTLIA.AUDITRECORDID)

            union all

            select -- history from REVENUESPLIT_EXT audit tables
              -- Audit booking
            RSEA.AUDITID
        ,RSEA.AUDITRECORDID RE_RECORDID
        ,RSEA.AUDITRECORDID
        ,RSEA.AUDITTYPECODE
        ,RSEA.AUDITKEY
        ,RSEA.AUDITCHANGEAGENTID
        ,RSEA.AUDITDATE
        ,RSEA.AUDITTYPE
        -- Audited columns
        ,null [REVENUEID]
        ,RSEA.DESIGNATIONID
        ,null [AMOUNT]
        ,RSEA.ADDEDBYID
        ,RSEA.CHANGEDBYID
        ,RSEA.DATEADDED
        ,RSEA.DATECHANGED
        ,RSEA.TYPECODE
        ,RSEA.APPLICATIONCODE
        ,null [BASECURRENCYID]
        ,null [ORGANIZATIONAMOUNT]
        ,null [ORGANIZATIONEXCHANGERATEID]
        ,null [TRANSACTIONAMOUNT]
        ,null [TRANSACTIONCURRENCYID]
        ,null [BASEEXCHANGERATEID]
        ,RSEA.OVERRIDEBUSINESSUNITS
        ,RSEA.REVENUESPLITBUSINESSUNITOVERRIDECODEID
                -- counters
                ,ROW_NUMBER() over (PARTITION by RSEA.AUDITRECORDID order by RSEA.AUDITDATE, RSEA.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by RSEA.AUDITRECORDID order by RSEA.AUDITDATE, RSEA.AUDITTYPECODE desc) [RE_COUNT]
            from dbo.REVENUESPLIT_EXTAUDIT RSEA
        ) T
    )  

  select 
      -- Audit booking
      CTE1.AUDITID
        ,CTE1.AUDITRECORDID
        ,CTE1.AUDITKEY
        ,CTE1.AUDITCHANGEAGENTID
        ,CTE1.AUDITDATE
        ,CTE1.AUDITTYPECODE
        ,CTE1.AUDITTYPE
        -- Audited columns
        ,coalesce(CTE1.REVENUEID, CTE2.REVENUEID, case when CTE2.AUDITID is null then FT.FINANCIALTRANSACTIONID else null end ) REVENUEID
    ,coalesce(CTE1.DESIGNATIONID, CTE2.DESIGNATIONID, case when CTE2.AUDITID is null then RE.DESIGNATIONID else null end ) DESIGNATIONID
    ,coalesce(CTE1.TRANSACTIONAMOUNT, CTE2.TRANSACTIONAMOUNT, case when CTE2.AUDITID is null then FT.TRANSACTIONAMOUNT else null end ) AMOUNT
    ,coalesce(CTE1.ADDEDBYID, CTE2.ADDEDBYID, case when CTE2.AUDITID is null then FT.ADDEDBYID else null end ) ADDEDBYID
    ,coalesce(CTE1.CHANGEDBYID, CTE2.CHANGEDBYID, case when CTE2.AUDITID is null then FT.CHANGEDBYID else null end ) CHANGEDBYID
    ,coalesce(CTE1.DATEADDED, CTE2.DATEADDED, case when CTE2.AUDITID is null then FT.DATEADDED else null end ) DATEADDED
    ,coalesce(CTE1.DATECHANGED, CTE2.DATECHANGED, case when CTE2.AUDITID is null then FT.DATECHANGED else null end ) DATECHANGED
    ,coalesce(CTE1.TYPECODE, CTE2.TYPECODE, case when CTE2.AUDITID is null then FT.TYPECODE else null end ) TYPECODE
    ,coalesce(CTE1.APPLICATIONCODE, CTE2.APPLICATIONCODE, case when CTE2.AUDITID is null then RE.APPLICATIONCODE else null end ) APPLICATIONCODE
    ,coalesce(CTE1.BASECURRENCYID, CTE2.BASECURRENCYID) BASECURRENCYID
    ,coalesce(CTE1.ORGANIZATIONAMOUNT, CTE2.ORGANIZATIONAMOUNT, case when CTE2.AUDITID is null then FT.ORGAMOUNT else null end ) ORGANIZATIONAMOUNT
    ,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID) ORGANIZATIONEXCHANGERATEID
    ,coalesce(CTE1.TRANSACTIONAMOUNT, CTE2.TRANSACTIONAMOUNT, case when CTE2.AUDITID is null then FT.TRANSACTIONAMOUNT else null end ) TRANSACTIONAMOUNT
    ,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID) TRANSACTIONCURRENCYID
    ,coalesce(CTE1.BASEEXCHANGERATEID, CTE2.BASEEXCHANGERATEID) BASEEXCHANGERATEID
    ,coalesce(CTE1.OVERRIDEBUSINESSUNITS, CTE2.OVERRIDEBUSINESSUNITS, case when CTE2.AUDITID is null then RE.OVERRIDEBUSINESSUNITS else null end ) OVERRIDEBUSINESSUNITS
    ,coalesce(CTE1.REVENUESPLITBUSINESSUNITOVERRIDECODEID, CTE2.REVENUESPLITBUSINESSUNITOVERRIDECODEID, case when CTE2.AUDITID is null then RE.REVENUESPLITBUSINESSUNITOVERRIDECODEID else null end ) REVENUESPLITBUSINESSUNITOVERRIDECODEID
    from Audit_CTE [CTE1]
    left outer join Audit_CTE [CTE2] on (CTE1.AUDITRECORDID = CTE2.AUDITRECORDID) and (CTE1.AUDITTYPECODE != 2 and (((CTE1.[RANK] - 1 = CTE2.[RANK]) and (CTE2.RE_COUNT = 1)) or ((CTE1.[RANK] + 1 = CTE2.[RANK]) and (CTE2.FT_COUNT = 1))) or (CTE1.AUDITTYPECODE = 2 and CTE2.AUDITTYPECODE = 2 and CTE1.AUDITID != CTE2.AUDITID))
    left outer join dbo.FINANCIALTRANSACTIONLINEITEM FT on FT.ID = CTE1.AUDITRECORDID
    left outer join dbo.REVENUESPLIT_EXT RE on RE.ID = CTE1.AUDITRECORDID;