UFN_GET_PROPERTYDETAILAUDIT_FROM_FTM

CREATE function dbo.UFN_GET_PROPERTYDETAILAUDIT_FROM_FTM() returns table 
return

    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.PROPERTYSUBTYPECODEID
            ,T.SALEDATE
            ,T.SALEAMOUNT
            ,T.BROKERFEE
            ,T.SALEPOSTDATE
            ,T.SALEPOSTSTATUSCODE
            ,T.ADDEDBYID
            ,T.CHANGEDBYID
            ,T.DATEADDED
            ,T.DATECHANGED
            ,T.ORGANIZATIONSALEAMOUNT
            ,T.ORGANIZATIONBROKERFEE
            ,T.TRANSACTIONSALEAMOUNT
            ,T.TRANSACTIONBROKERFEE
            ,T.BASECURRENCYID
            ,T.ORGANIZATIONEXCHANGERATEID
            ,T.TRANSACTIONCURRENCYID
            ,T.BASEEXCHANGERATEID
        from (
            select FT.AUDITID
                ,null [RE_RECORDID]
                ,FT.AUDITRECORDID
                ,FT.AUDITTYPECODE
                ,ROW_NUMBER() over (PARTITION by FT.AUDITRECORDID order by FT.AUDITDATE, FT.AUDITTYPECODE asc) [FT_COUNT]
                ,ROW_NUMBER() over (PARTITION by FT.AUDITRECORDID order by FT.AUDITDATE, FT.AUDITTYPECODE desc) [RE_COUNT]
                ,FT.AUDITKEY
                ,FT.AUDITCHANGEAGENTID
                ,FT.AUDITDATE
                ,FT.AUDITTYPE
                ,null [PROPERTYSUBTYPECODEID]
                ,null [SALEDATE]
                ,case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FT.BASEAMOUNT else -1 * FT.BASEAMOUNT end as SALEAMOUNT
                ,null [BROKERFEE]
                ,FT.POSTDATE as SALEPOSTDATE
                ,isnull(cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint), 2) as SALEPOSTSTATUSCODE
                ,FT.ADDEDBYID
                ,FT.CHANGEDBYID
                ,FT.DATEADDED
                ,FT.DATECHANGED
                ,case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FT.ORGAMOUNT else -1 * FT.ORGAMOUNT end as ORGANIZATIONSALEAMOUNT
                ,null [ORGANIZATIONBROKERFEE]
                ,case PROPERTYDETAIL_EXT.ISNEGATIVE when 0 then FT.TRANSACTIONAMOUNT else -1 * FT.TRANSACTIONAMOUNT end as TRANSACTIONSALEAMOUNT
                ,null [TRANSACTIONBROKERFEE]
                ,CS.BASECURRENCYID
                ,FT.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
                ,FT.TRANSACTIONCURRENCYID
                ,FT.BASEEXCHANGERATEID
            from dbo.FINANCIALTRANSACTIONAUDIT FT
            left join dbo.PROPERTYDETAIL_EXT on FT.AUDITRECORDID = PROPERTYDETAIL_EXT.ID
            left join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
            left join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
            where FT.TYPECODE = 22

            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
                ,RE.PROPERTYSUBTYPECODEID
                ,RE.SALEDATE
                ,null [SALEAMOUNT]
                ,RE.TRANSACTIONBROKERFEE as BROKERFEE
                ,null [SALEPOSTDATE]
                ,null [SALEPOSTSTATUSCODE]
                ,RE.ADDEDBYID
                ,RE.CHANGEDBYID
                ,RE.DATEADDED
                ,RE.DATECHANGED
                ,null [ORGANIZATIONSALEAMOUNT]
                ,RE.ORGANIZATIONBROKERFEE
                ,null [TRANSACTIONSALEAMOUNT]
                ,RE.BROKERFEE as TRANSACTIONBROKERFEE
                ,CS.BASECURRENCYID
                ,null [ORGANIZATIONEXCHANGERATEID]
                ,null [TRANSACTIONCURRENCYID]
                ,null [BASEEXCHANGERATEID]
            from dbo.PROPERTYDETAIL_EXTAUDIT RE
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = RE.AUDITRECORDID
            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.PROPERTYSUBTYPECODEID, CTE2.PROPERTYSUBTYPECODEID, CASE WHEN CTE2.AUDITID is null THEN RE.PROPERTYSUBTYPECODEID ELSE NULL END) PROPERTYSUBTYPECODEID
        ,coalesce(CTE1.SALEDATE, CTE2.SALEDATE, CASE WHEN CTE2.AUDITID is null THEN RE.SALEDATE ELSE NULL END) SALEDATE
        ,coalesce(CTE1.SALEAMOUNT, CTE2.SALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN case RE.ISNEGATIVE when 0 then FT.BASEAMOUNT else -1 * FT.BASEAMOUNT end ELSE NULL END) SALEAMOUNT
        ,coalesce(CTE1.BROKERFEE, CTE2.BROKERFEE, CASE WHEN CTE2.AUDITID is null THEN RE.TRANSACTIONBROKERFEE ELSE NULL END) BROKERFEE
        ,coalesce(CTE1.SALEPOSTDATE, CTE2.SALEPOSTDATE, CASE WHEN CTE2.AUDITID is null THEN FT.POSTDATE ELSE NULL END) SALEPOSTDATE
        ,coalesce(CTE1.SALEPOSTSTATUSCODE, CTE2.SALEPOSTSTATUSCODE, CASE WHEN CTE2.AUDITID is null THEN cast(case FT.POSTSTATUSCODE when 2 then 0 when 1 then 1 when 3 then 2 end as tinyint) ELSE NULL END) SALEPOSTSTATUSCODE
        ,CTE1.ADDEDBYID
        ,CTE1.CHANGEDBYID
        ,CTE1.DATEADDED
        ,CTE1.DATECHANGED
        ,coalesce(CTE1.ORGANIZATIONSALEAMOUNT, CTE2.ORGANIZATIONSALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN case RE.ISNEGATIVE when 0 then FT.ORGAMOUNT else -1 * FT.ORGAMOUNT end ELSE NULL END) ORGANIZATIONSALEAMOUNT
        ,coalesce(CTE1.ORGANIZATIONBROKERFEE, CTE2.ORGANIZATIONBROKERFEE, CASE WHEN CTE2.AUDITID is null THEN RE.ORGANIZATIONBROKERFEE ELSE NULL END) ORGANIZATIONBROKERFEE
        ,coalesce(CTE1.TRANSACTIONSALEAMOUNT, CTE2.TRANSACTIONSALEAMOUNT, CASE WHEN CTE2.AUDITID is null THEN case RE.ISNEGATIVE when 0 then FT.TRANSACTIONAMOUNT else -1 * FT.TRANSACTIONAMOUNT end ELSE NULL END) TRANSACTIONSALEAMOUNT
        ,coalesce(CTE1.TRANSACTIONBROKERFEE, CTE2.TRANSACTIONBROKERFEE, CASE WHEN CTE2.AUDITID is null THEN RE.BROKERFEE ELSE NULL END) TRANSACTIONBROKERFEE
        ,coalesce(CTE1.BASECURRENCYID, CTE2.BASECURRENCYID) BASECURRENCYID
        ,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN FT.ORGEXCHANGERATEID ELSE NULL END) ORGANIZATIONEXCHANGERATEID
        ,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONCURRENCYID ELSE NULL END) TRANSACTIONCURRENCYID
        ,coalesce(CTE1.BASEEXCHANGERATEID, CTE2.BASEEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN FT.BASEEXCHANGERATEID 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.FINANCIALTRANSACTION FT on FT.ID = CTE1.AUDITRECORDID
    left join dbo.PROPERTYDETAIL_EXT RE on RE.ID = CTE1.AUDITRECORDID;