UFN_AUDIT_GETDETAILSFORREVENUESPLIT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_AUDIT_GETDETAILSFORREVENUESPLIT](@REVENUEID uniqueidentifier)
returns @RESULTS table
(RECORDID uniqueidentifier
,AUDITDATE datetime
,CHANGEDBYUSER varchar(128)
,PROCESS varchar(255)
,FIELD varchar(128)
,OLD varchar(4000)
,NEW varchar(4000)
,SEQUENCE int
,[ACTION] varchar(8)
,CHANGEDBYAPP varchar(200))
as
begin
declare @LineItems table (ID uniqueidentifier, primary key(ID));
insert into @LineItems
select distinct T.ID from (
select LI.ID
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and LI.REVERSEDLINEITEMID is null
union all
select LI.AUDITRECORDID
from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LI
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.AUDITRECORDID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and LI.REVERSEDLINEITEMID is null
union all
select LI.AUDITRECORDID
from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LI
inner join dbo.REVENUESPLIT_EXTAUDIT RSEA on RSEA.AUDITRECORDID = LI.AUDITRECORDID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and LI.REVERSEDLINEITEMID is null) T
insert into @RESULTS
(RECORDID
,AUDITDATE
,CHANGEDBYUSER
,PROCESS
,FIELD
,OLD
,NEW
,SEQUENCE
,[ACTION]
,CHANGEDBYAPP)
select
V4.RECORDID
,V4.AUDITDATE
,CHANGEAGENT.USERNAME as CHANGEDBYUSER
,CHANGEAGENT.PROCESSDISPLAYNAME as PROCESS
,V4.FIELD
,V4.OLD
,V4.NEW
,30 as SEQUENCE
,V4.[ACTION]
,CHANGEAGENT.APPLICATIONNAME as CHANGEDBYAPP
from
(select
V3.RECORDID
,V3.AUDITDATE
,case V3.AUDITTYPECODE2 when 2 then 'Delete' when 0 then 'Update' else case when V3.AUDITTYPECODE1 = 999 then 'Delete' else 'Insert' end end as [ACTION]
,'Application details:' + case NUMS.NUM when 1 then 'AMOUNT' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' when 999 then isnull(convert(varchar,V3.AMOUNT1,1),'n/a') else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT2,1),'n/a') end end
end as OLD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.AUDITTYPECODE1 = 999 then 'n/a' else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT1,1),'n/a') end end end
end as NEW
,ChangeAgentID
from
(select
isnull(V1.AUDITRECORDID, V2.AUDITRECORDID) as RECORDID
,isnull(V1.AUDITCHANGEAGENTID,V2.AUDITCHANGEAGENTID) as CHANGEAGENTID
,isnull(V1.AUDITDATE, V2.AUDITDATE) as AUDITDATE
,V1.AUDITTYPECODE as AUDITTYPECODE1
,V2.AUDITTYPECODE as AUDITTYPECODE2
,V1.BASEAMOUNT as AMOUNT1
,V2.BASEAMOUNT as AMOUNT2
from
(select
LIA.AUDITRECORDID
,LIA.AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.DATECHANGED as AUDITDATE
,LIA.AUDITTYPECODE
,LIA.BASEAMOUNT
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
inner join @LineItems LI on LI.ID = LIA.AUDITRECORDID
where LIA.AUDITTYPECODE = 1
union all
select
LI.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LI.ADDEDBYID
,LI.DATEADDED
,-1 as AUDITTYPECODE
,LI.BASEAMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join @LineItems L on LI.ID = L.ID
where LI.ID not in (select LIA.AUDITRECORDID
from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.AUDITTYPECODE = 0)
union all
select
AUDITRECORDID
,AUDITKEY
,ADDEDBYID
,AUDITDATE
,AUDITTYPECODE
,BASEAMOUNT
from (
select
LIA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LIA.ADDEDBYID
,LIA.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,LIA.BASEAMOUNT
,ROW_NUMBER() over (partition by LIA.AUDITRECORDID order by LIA.AUDITDATE asc) SEQUENCE
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
inner join @LineItems LI on LI.ID = LIA.AUDITRECORDID
where LIA.AUDITTYPECODE = 0) BEFOREUPDATES
where BEFOREUPDATES.SEQUENCE = 1
union all
select
LIA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.DATEADDED [AUDITDATE]
,LIA.AUDITTYPECODE
,LIA.BASEAMOUNT
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
inner join @LineItems LI on LI.ID = LIA.AUDITRECORDID
where LIA.AUDITTYPECODE = 2 and LIA.AUDITRECORDID not in (
select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @REVENUEID
union all
select AUDITRECORDID from FINANCIALTRANSACTIONLINEITEMAUDIT where FINANCIALTRANSACTIONLINEITEMAUDIT.AUDITTYPECODE != 2 and FINANCIALTRANSACTIONLINEITEMAUDIT.FINANCIALTRANSACTIONID = @REVENUEID)
union all
select
LI.ID [AUDITRECORDID]
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LI.CHANGEDBYID [AUDITCHANGEAGENTID]
,LI.DELETEDON [AUDITDATE]
,999 [AUDITTYPECODE]
,LI.BASEAMOUNT
from @LineItems Filter
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = Filter.ID
where LI.DELETEDON is not null
) V1
full outer join
(select
LIA.AUDITRECORDID
,LIA.AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.AUDITDATE
,LIA.AUDITTYPECODE
,LIA.BASEAMOUNT
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
inner join @LineItems LI on LI.ID = LIA.AUDITRECORDID
where LIA.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
where
(V1.BASEAMOUNT != V2.BASEAMOUNT
or (V1.BASEAMOUNT is null and V2.BASEAMOUNT is not null)
or (V1.BASEAMOUNT is not null and V2.BASEAMOUNT is null)
)
) V3
cross join (
select 1 as NUM) as NUMS) V4
inner join dbo.CHANGEAGENT on V4.CHANGEAGENTID = CHANGEAGENT.ID
where OLD is not null
union all
select
V4.RECORDID
,V4.AUDITDATE
,CHANGEAGENT.USERNAME as CHANGEDBYUSER
,CHANGEAGENT.PROCESSDISPLAYNAME as PROCESS
,V4.FIELD
,V4.OLD
,V4.NEW
,30 as SEQUENCE
,V4.[ACTION]
,CHANGEAGENT.APPLICATIONNAME as CHANGEDBYAPP
from
(select
V3.RECORDID
,V3.AUDITDATE
,case V3.AUDITTYPECODE2 when 2 then 'Delete' when 0 then 'Update' else case when V3.AUDITTYPECODE1 = 999 then 'Delete' else 'Insert' end end as [ACTION]
,'Application details:' + case NUMS.NUM when 1 then 'DESIGNATIONID' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' when 999 then isnull(V3.DESIGNATIONNAME1,'n/a') else case when V3.DESIGNATIONID1 = V3.DESIGNATIONID2 then null else isnull(V3.DESIGNATIONNAME2,'n/a') end end
end as OLD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.AUDITTYPECODE1 = 999 then 'n/a' else case when V3.DESIGNATIONID1 = V3.DESIGNATIONID2 then null else isnull(V3.DESIGNATIONNAME1,'n/a') end end end
end as NEW
,ChangeAgentID
from
(select
isnull(V1.AUDITRECORDID, V2.AUDITRECORDID) as RECORDID
,isnull(V1.AUDITCHANGEAGENTID,V2.AUDITCHANGEAGENTID) as CHANGEAGENTID
,isnull(V1.AUDITDATE, V2.AUDITDATE) as AUDITDATE
,V1.AUDITTYPECODE as AUDITTYPECODE1
,V2.AUDITTYPECODE as AUDITTYPECODE2
,V1.DESIGNATIONID as DESIGNATIONID1
,V2.DESIGNATIONID as DESIGNATIONID2
,DESIGNATION.NAME as DESIGNATIONNAME1
,DESIGNATION2.NAME as DESIGNATIONNAME2
from
(select
RSA.AUDITRECORDID
,RSA.AUDITKEY
,RSA.AUDITCHANGEAGENTID
,RSA.DATECHANGED as AUDITDATE
,RSA.AUDITTYPECODE
,RSA.DESIGNATIONID
from REVENUESPLIT_EXTAUDIT RSA
inner join @LineItems LI on LI.ID = RSA.AUDITRECORDID
where RSA.AUDITTYPECODE = 1
union all
select
RS.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RS.ADDEDBYID
,RS.DATEADDED
,-1 as AUDITTYPECODE
,RS.DESIGNATIONID
from REVENUESPLIT_EXT RS
inner join @LineItems LI on LI.ID = RS.ID
where RS.ID not in (select RSA.AUDITRECORDID
from dbo.REVENUESPLIT_EXTAUDIT RSA
inner join @LineItems LI on LI.ID = RSA.AUDITRECORDID
where RSA.AUDITTYPECODE = 0)
union all
select
AUDITRECORDID
,AUDITKEY
,AUDITCHANGEAGENTID
,AUDITDATE
,AUDITTYPECODE
,DESIGNATIONID
from (
select
RSA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RSA.AUDITCHANGEAGENTID
,RSA.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,RSA.DESIGNATIONID
,ROW_NUMBER() over (partition by RSA.AUDITRECORDID order by RSA.AUDITDATE asc) SEQUENCE
from REVENUESPLIT_EXTAUDIT RSA
inner join @LineItems LI on LI.ID = RSA.AUDITRECORDID
where RSA.AUDITTYPECODE = 0) BEFOREUPDATES
where BEFOREUPDATES.SEQUENCE = 1
union all
select
RSA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RSA.AUDITCHANGEAGENTID
,RSA.DATEADDED [AUDITDATE]
,RSA.AUDITTYPECODE
,RSA.DESIGNATIONID
from REVENUESPLIT_EXTAUDIT RSA
inner join @LineItems LI on LI.ID = RSA.AUDITRECORDID
where RSA.AUDITTYPECODE = 2 and RSA.AUDITRECORDID not in (
select ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = @REVENUEID
union all
select AUDITRECORDID
from REVENUESPLIT_EXTAUDIT
inner join @LineItems LI on LI.ID = AUDITRECORDID
where AUDITTYPECODE != 2)
union all
select
LI.ID [AUDITRECORDID]
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LI.CHANGEDBYID [AUDITCHANGEAGENTID]
,LI.DELETEDON [AUDITDATE]
,999 [AUDITTYPECODE]
,RSE.DESIGNATIONID
from @LineItems Filter
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = Filter.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
where LI.DELETEDON is not null
) V1
full outer join
(select
RSA.AUDITRECORDID
,RSA.AUDITKEY
,RSA.AUDITCHANGEAGENTID
,RSA.AUDITDATE
,RSA.AUDITTYPECODE
,RSA.DESIGNATIONID
from REVENUESPLIT_EXTAUDIT RSA
inner join @LineItems LI on LI.ID = RSA.AUDITRECORDID
where RSA.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
left join dbo.DESIGNATION on V1.DESIGNATIONID = DESIGNATION.ID
left join dbo.DESIGNATION as DESIGNATION2 on V2.DESIGNATIONID = DESIGNATION2.ID
where
(V1.DESIGNATIONID != V2.DESIGNATIONID
or (V1.DESIGNATIONID is null and V2.DESIGNATIONID is not null)
or (V1.DESIGNATIONID is not null and V2.DESIGNATIONID is null)
)
) V3
cross join (
select 1 as NUM) as NUMS) V4
inner join dbo.CHANGEAGENT on V4.CHANGEAGENTID = CHANGEAGENT.ID
where OLD is not null;
return;
end