UFN_GET_REVENUEAUDIT_FROM_FTM
Return
| Return Type |
|---|
| table |
Definition
Copy
CREATE function dbo.UFN_GET_REVENUEAUDIT_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.CONSTITUENTID
,T.[DATE]
,T.BATCHNUMBER
,T.POSTDATE
,T.DONOTPOST
,T.DONOTRECEIPT
,T.RECEIPTAMOUNT
,T.AMOUNT
,T.SOURCECODE
,T.FINDERNUMBER
,T.APPEALID
,T.MAILINGID
,T.CHANNELCODEID
,T.GIVENANONYMOUSLY
,T.DONOTACKNOWLEDGE
,T.BENEFITSWAIVED
,T.ADDEDBYID
,T.CHANGEDBYID
,T.DATEADDED
,T.DATECHANGED
,T.RECEIPTTYPECODE
,T.NEEDSRERECEIPT
,T.TRANSACTIONTYPECODE
,T.ELIGIBLEFORMATCHINGGIFTCLAIM
,T.CUSTOMIDENTIFIER
,T.SEQUENCEID
,T.ISREIMBURSABLE
,T.BASECURRENCYID
,T.ORGANIZATIONAMOUNT
,T.TRANSACTIONAMOUNT
,T.TRANSACTIONCURRENCYID
,T.ORGANIZATIONEXCHANGERATEID
,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
,FT.CONSTITUENTID
,cast(FT.[DATE] as datetime) [DATE]
,null [BATCHNUMBER]
,FT.POSTDATE
,CASE WHEN FT.POSTSTATUSCODE = 3 THEN 1 ELSE 0 END [DONOTPOST]
,null [DONOTRECEIPT]
,null [RECEIPTAMOUNT]
,FT.BASEAMOUNT [AMOUNT]
,null [SOURCECODE]
,null [FINDERNUMBER]
,null [APPEALID]
,null [MAILINGID]
,null [CHANNELCODEID]
,null [GIVENANONYMOUSLY]
,null [DONOTACKNOWLEDGE]
,null [BENEFITSWAIVED]
,FT.ADDEDBYID
,FT.CHANGEDBYID
,FT.DATEADDED
,FT.DATECHANGED
,null [RECEIPTTYPECODE]
,null [NEEDSRERECEIPT]
,FT.TYPECODE [TRANSACTIONTYPECODE]
,null [ELIGIBLEFORMATCHINGGIFTCLAIM]
,FT.USERDEFINEDID [CUSTOMIDENTIFIER]
,null [SEQUENCEID]
,null [ISREIMBURSABLE]
,CS.BASECURRENCYID
,FT.ORGAMOUNT [ORGANIZATIONAMOUNT]
,FT.TRANSACTIONAMOUNT [TRANSACTIONAMOUNT]
,FT.TRANSACTIONCURRENCYID
,FT.ORGEXCHANGERATEID [ORGANIZATIONEXCHANGERATEID]
,FT.BASEEXCHANGERATEID
from dbo.FINANCIALTRANSACTIONAUDIT FT
left join dbo.PDACCOUNTSYSTEM S on FT.PDACCOUNTSYSTEMID = S.ID
left join dbo.CURRENCYSET CS on CS.ID = S.CURRENCYSETID
where FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
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 [CONSTITUENTID]
,null [DATE]
,RE.BATCHNUMBER
,null [POSTDATE]
,null [DONOTPOST]
,RE.DONOTRECEIPT
,RE.RECEIPTAMOUNT
,FT.BASEAMOUNT [AMOUNT]
,RE.SOURCECODE
,RE.FINDERNUMBER
,RE.APPEALID
,RE.MAILINGID
,RE.CHANNELCODEID
,RE.GIVENANONYMOUSLY
,RE.DONOTACKNOWLEDGE
,RE.BENEFITSWAIVED
,RE.ADDEDBYID
,RE.CHANGEDBYID
,RE.DATEADDED
,RE.DATECHANGED
,RE.RECEIPTTYPECODE
,RE.NEEDSRERECEIPT
,null [TRANSACTIONTYPECODE]
,RE.ELIGIBLEFORMATCHINGGIFTCLAIM
,FT.USERDEFINEDID
,1
,RE.ISREIMBURSABLE
,CS.BASECURRENCYID
,FT.ORGAMOUNT [ORGANIZATIONAMOUNT]
,null [TRANSACTIONAMOUNT]
,null TRANSACTIONCURRENCYID
,null [ORGANIZATIONEXCHANGERATEID]
,null [BASEEXCHANGERATEID]
from dbo.REVENUE_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.CONSTITUENTID, CTE2.CONSTITUENTID, CASE WHEN CTE2.AUDITID is null THEN FT.CONSTITUENTID ELSE NULL END) CONSTITUENTID
,coalesce(CTE1.[DATE], CTE2.[DATE], CASE WHEN CTE2.AUDITID is null THEN cast(FT.[DATE] as datetime) ELSE NULL END) [DATE]
,coalesce(CTE1.BATCHNUMBER, CTE2.BATCHNUMBER, CASE WHEN CTE2.AUDITID is null THEN RE.BATCHNUMBER ELSE NULL END) BATCHNUMBER
,coalesce(CTE1.POSTDATE, CTE2.POSTDATE, CASE WHEN CTE2.AUDITID is null THEN FT.POSTDATE ELSE NULL END) POSTDATE
,coalesce(CTE1.DONOTPOST, CTE2.DONOTPOST, CASE WHEN CTE2.AUDITID is null THEN CASE WHEN FT.POSTSTATUSCODE = 3 THEN 1 ELSE 0 END ELSE NULL END) DONOTPOST
,coalesce(CTE1.DONOTRECEIPT, CTE2.DONOTRECEIPT, CASE WHEN CTE2.AUDITID is null THEN RE.DONOTRECEIPT ELSE NULL END) DONOTRECEIPT
,coalesce(CTE1.RECEIPTAMOUNT, CTE2.RECEIPTAMOUNT, CASE WHEN CTE2.AUDITID is null THEN RE.RECEIPTAMOUNT ELSE NULL END) RECEIPTAMOUNT
,coalesce(CTE1.AMOUNT, CTE2.AMOUNT, CASE WHEN CTE2.AUDITID is null THEN FT.BASEAMOUNT ELSE NULL END) AMOUNT
,coalesce(CTE1.SOURCECODE, CTE2.SOURCECODE, CASE WHEN CTE2.AUDITID is null THEN RE.SOURCECODE ELSE NULL END) SOURCECODE
,coalesce(CTE1.FINDERNUMBER, CTE2.FINDERNUMBER, CASE WHEN CTE2.AUDITID is null THEN RE.FINDERNUMBER ELSE NULL END) FINDERNUMBER
,coalesce(CTE1.APPEALID, CTE2.APPEALID, CASE WHEN CTE2.AUDITID is null THEN RE.APPEALID ELSE NULL END) APPEALID
,coalesce(CTE1.MAILINGID, CTE2.MAILINGID, CASE WHEN CTE2.AUDITID is null THEN RE.MAILINGID ELSE NULL END) MAILINGID
,coalesce(CTE1.CHANNELCODEID, CTE2.CHANNELCODEID, CASE WHEN CTE2.AUDITID is null THEN RE.CHANNELCODEID ELSE NULL END) CHANNELCODEID
,coalesce(CTE1.GIVENANONYMOUSLY, CTE2.GIVENANONYMOUSLY, CASE WHEN CTE2.AUDITID is null THEN RE.GIVENANONYMOUSLY ELSE NULL END) GIVENANONYMOUSLY
,coalesce(CTE1.DONOTACKNOWLEDGE, CTE2.DONOTACKNOWLEDGE, CASE WHEN CTE2.AUDITID is null THEN RE.DONOTACKNOWLEDGE ELSE NULL END) DONOTACKNOWLEDGE
,coalesce(CTE1.BENEFITSWAIVED, CTE2.BENEFITSWAIVED, CASE WHEN CTE2.AUDITID is null THEN RE.BENEFITSWAIVED ELSE NULL END) BENEFITSWAIVED
,CTE1.ADDEDBYID
,CTE1.CHANGEDBYID
,CTE1.DATEADDED
,CTE1.DATECHANGED
,coalesce(CTE1.RECEIPTTYPECODE, CTE2.RECEIPTTYPECODE, CASE WHEN CTE2.AUDITID is null THEN RE.RECEIPTTYPECODE ELSE NULL END) RECEIPTTYPECODE
,coalesce(CTE1.NEEDSRERECEIPT, CTE2.NEEDSRERECEIPT, CASE WHEN CTE2.AUDITID is null THEN RE.NEEDSRERECEIPT ELSE NULL END) NEEDSRERECEIPT
,coalesce(CTE1.TRANSACTIONTYPECODE, CTE2.TRANSACTIONTYPECODE, CASE WHEN CTE2.AUDITID is null THEN FT.TYPECODE ELSE NULL END) TRANSACTIONTYPECODE
,coalesce(CTE1.ELIGIBLEFORMATCHINGGIFTCLAIM, CTE2.ELIGIBLEFORMATCHINGGIFTCLAIM, CASE WHEN CTE2.AUDITID is null THEN RE.ELIGIBLEFORMATCHINGGIFTCLAIM ELSE NULL END) ELIGIBLEFORMATCHINGGIFTCLAIM
,coalesce(CTE1.CUSTOMIDENTIFIER, CTE2.CUSTOMIDENTIFIER, CASE WHEN CTE2.AUDITID is null THEN FT.USERDEFINEDID ELSE NULL END) CUSTOMIDENTIFIER
,coalesce(CTE1.SEQUENCEID, CTE2.SEQUENCEID, CASE WHEN CTE2.AUDITID is null THEN 1 ELSE NULL END) SEQUENCEID
,coalesce(CTE1.ISREIMBURSABLE, CTE2.ISREIMBURSABLE, CASE WHEN CTE2.AUDITID is null THEN RE.ISREIMBURSABLE ELSE NULL END) ISREIMBURSABLE
,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.TRANSACTIONAMOUNT, CTE2.TRANSACTIONAMOUNT, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONAMOUNT ELSE NULL END) TRANSACTIONAMOUNT
,coalesce(CTE1.TRANSACTIONCURRENCYID, CTE2.TRANSACTIONCURRENCYID, CASE WHEN CTE2.AUDITID is null THEN FT.TRANSACTIONCURRENCYID ELSE NULL END) TRANSACTIONCURRENCYID
,coalesce(CTE1.ORGANIZATIONEXCHANGERATEID, CTE2.ORGANIZATIONEXCHANGERATEID, CASE WHEN CTE2.AUDITID is null THEN FT.ORGEXCHANGERATEID ELSE NULL END) ORGANIZATIONEXCHANGERATEID
,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.REVENUE_EXT RE on RE.ID = CTE1.AUDITRECORDID;