REVENUEGLDISTRIBUTIONAUDIT

CREATE view dbo.REVENUEGLDISTRIBUTIONAUDIT 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.GLPAYMENTMETHODREVENUETYPEMAPPINGID
            ,T.PROJECT
            ,T.REFERENCE
            ,T.AMOUNT
            ,T.ADDEDBYID
            ,T.CHANGEDBYID
            ,T.DATEADDED
            ,T.DATECHANGED
            ,T.ACCOUNT
            ,T.TRANSACTIONTYPECODE
            ,T.GLTRANSACTIONID
            ,T.OUTDATED
            ,T.BASECURRENCYID
            ,T.ORGANIZATIONAMOUNT
            ,T.ORGANIZATIONEXCHANGERATEID
            ,T.TRANSACTIONAMOUNT
            ,T.TRANSACTIONCURRENCYID
            ,T.BASEEXCHANGERATEID
        from (
            select JE.AUDITID
                ,null [RE_RECORDID]
                ,JE.AUDITRECORDID
                ,JE.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by JE.AUDITRECORDID order by JE.AUDITDATE, JE.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by JE.AUDITRECORDID order by JE.AUDITDATE, JE.AUDITTYPECODE desc) [RE_COUNT]
                ,JE.AUDITKEY
                ,JE.AUDITCHANGEAGENTID
                ,JE.AUDITDATE
                ,JE.AUDITTYPE
                ,FTLI.FINANCIALTRANSACTIONID as REVENUEID
                ,null as GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,null as PROJECT
                ,JE.COMMENT as REFERENCE
                ,JE.BASEAMOUNT as AMOUNT
                ,JE.ADDEDBYID
                ,JE.CHANGEDBYID
                ,JE.DATEADDED
                ,JE.DATECHANGED
                ,null as ACCOUNT
                ,JE.TRANSACTIONTYPECODE
                ,JE.AUDITRECORDID as GLTRANSACTIONID
                ,null OUTDATED
                ,V.BASECURRENCYID
                ,JE.ORGAMOUNT as ORGANIZATIONAMOUNT
                ,null as ORGANIZATIONEXCHANGERATEID
                ,JE.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT
                ,JE.TRANSACTIONCURRENCYID
                ,null BASEEXCHANGERATEID
            from dbo.JOURNALENTRYAUDIT JE
            left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FTLI.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID

            union all

            select JE.AUDITID
                ,JE.AUDITRECORDID [RE_RECORDID]
                ,JE.AUDITRECORDID
                ,JE.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by JE.AUDITRECORDID order by JE.AUDITDATE, JE.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by JE.AUDITRECORDID order by JE.AUDITDATE, JE.AUDITTYPECODE desc) [RE_COUNT]
                ,JE.AUDITKEY
                ,JE.AUDITCHANGEAGENTID
                ,JE.AUDITDATE
                ,JE.AUDITTYPE
                ,null as REVENUEID
                ,JE.GLPAYMENTMETHODREVENUETYPEMAPPINGID
                ,JE.PROJECT
                ,null as REFERENCE
                ,null as AMOUNT
                ,JE.ADDEDBYID
                ,JE.CHANGEDBYID
                ,JE.DATEADDED
                ,JE.DATECHANGED
                ,JE.ACCOUNT
                ,null as TRANSACTIONTYPECODE
                ,null as GLTRANSACTIONID
                ,JE.OUTDATED
                ,V.BASECURRENCYID
                ,null as ORGANIZATIONAMOUNT
                ,JE.PRECALCORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
                ,null as TRANSACTIONAMOUNT
                ,null as TRANSACTIONCURRENCYID
                ,JE.PRECALCBASEEXCHANGERATEID BASEEXCHANGERATEID
            from dbo.JOURNALENTRY_EXTAUDIT JE
            left join dbo.JOURNALENTRY on JE.AUDITRECORDID = JOURNALENTRY.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
            left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FTLI.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
            where JE.TABLENAMECODE = 1
        ) 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.GLPAYMENTMETHODREVENUETYPEMAPPINGID, CTE2.GLPAYMENTMETHODREVENUETYPEMAPPINGID, CASE WHEN CTE2.AUDITID is null THEN RE.GLPAYMENTMETHODREVENUETYPEMAPPINGID ELSE NULL END) GLPAYMENTMETHODREVENUETYPEMAPPINGID
        ,coalesce(CTE1.PROJECT, CTE2.PROJECT, CASE WHEN CTE2.AUDITID is null THEN RE.PROJECT ELSE null end) PROJECT
        ,coalesce(CTE1.REFERENCE, CTE2.REFERENCE, CASE WHEN CTE2.AUDITID is null THEN JE.COMMENT ELSE null end) REFERENCE
        ,coalesce(CTE1.AMOUNT, CTE2.AMOUNT, CASE WHEN CTE2.AUDITID is null THEN JE.BASEAMOUNT ELSE NULL END) AMOUNT
        ,CTE1.ADDEDBYID
        ,CTE1.CHANGEDBYID
        ,CTE1.DATEADDED
        ,CTE1.DATECHANGED
        ,coalesce(CTE1.ACCOUNT, CTE2.ACCOUNT, CASE WHEN CTE2.AUDITID is null THEN RE.ACCOUNT ELSE NULL END) ACCOUNT
        ,coalesce(CTE1.TRANSACTIONTYPECODE, CTE2.TRANSACTIONTYPECODE, CASE WHEN CTE2.AUDITID is null THEN JE.TRANSACTIONTYPECODE ELSE NULL END) TRANSACTIONTYPECODE
        ,coalesce(CTE1.GLTRANSACTIONID, CTE2.GLTRANSACTIONID, CASE WHEN CTE2.AUDITID is null THEN JE.ID ELSE NULL END) GLTRANSACTIONID
        ,coalesce(CTE1.OUTDATED, CTE2.OUTDATED, CASE WHEN CTE2.AUDITID is null THEN RE.OUTDATED ELSE NULL END) OUTDATED
        ,coalesce(CTE1.BASECURRENCYID, CTE2.BASECURRENCYID) BASECURRENCYID
        ,coalesce(CTE1.ORGANIZATIONAMOUNT, CTE2.ORGANIZATIONAMOUNT, CASE WHEN CTE2.AUDITID is null THEN JE.ORGAMOUNT ELSE NULL END) ORGANIZATIONAMOUNT
        ,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN RE.PRECALCORGANIZATIONEXCHANGERATEID ELSE NULL END) ORGANIZATIONEXCHANGERATEID
        ,coalesce(CTE1.TRANSACTIONAMOUNT, CTE2.TRANSACTIONAMOUNT, CASE WHEN CTE2.AUDITID is null THEN JE.TRANSACTIONAMOUNT ELSE NULL END) TRANSACTIONAMOUNT
        ,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID, CASE WHEN CTE2.AUDITID is null THEN JE.TRANSACTIONCURRENCYID ELSE NULL END) TRANSACTIONCURRENCYID
        ,coalesce(CTE1.BASEEXCHANGERATEID, CTE2.BASEEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN RE.PRECALCBASEEXCHANGERATEID ELSE NULL END) BASEEXCHANGERATEID
    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.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 join dbo.JOURNALENTRY JE on JE.ID = CTE1.AUDITRECORDID
    left join dbo.FINANCIALTRANSACTIONLINEITEM FT on FT.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    left join dbo.JOURNALENTRY_EXT RE on RE.ID = CTE1.AUDITRECORDID;