UFN_AUDIT_GETDETAILSFORREVENUEBENEFIT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_AUDIT_GETDETAILSFORREVENUEBENEFIT(@REVENUEID uniqueidentifier)
returns table
as return
select
V4.RECORDID
,V4.AUDITDATE
,CHANGEAGENT.USERNAME as CHANGEDBYUSER
,CHANGEAGENT.PROCESSDISPLAYNAME as PROCESS
,V4.FIELD
,V4.OLD
,V4.NEW
,40 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 'Insert' end as [ACTION]
,'Benefit for:' + case NUMS.NUM when 1 then 'QUANTITY' when 2 then 'UNITVALUE' when 3 then 'DETAILS' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.QUANTITY1 = V3.QUANTITY2 then null else isnull(convert(varchar, V3.QUANTITY2),'n/a') end end
when 2 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.UNITVALUE1 = V3.UNITVALUE2 then null else isnull(convert(varchar, V3.UNITVALUE2),'n/a') end end
when 3 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.DETAILS1 = V3.DETAILS2 then null else isnull(V3.DETAILS2,'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.QUANTITY1 = V3.QUANTITY2 then null else isnull(convert(varchar, V3.QUANTITY1),'n/a') end end
when 2 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.UNITVALUE1 = V3.UNITVALUE2 then null else isnull(convert(varchar, V3.UNITVALUE1),'n/a') end end
when 3 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.DETAILS1 = V3.DETAILS2 then null else isnull(V3.DETAILS1,'n/a') 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.QUANTITY as QUANTITY1
,V2.QUANTITY as QUANTITY2
,V1.UNITVALUE as UNITVALUE1
,V2.UNITVALUE as UNITVALUE2
,V1.DETAILS as DETAILS1
,V2.DETAILS as DETAILS2
from
(select
LIA.AUDITRECORDID
,LIA.AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.DATECHANGED as AUDITDATE
,LIA.AUDITTYPECODE
,LIA.QUANTITY
,LIA.UNITVALUE
,isnull(Left(LIA.DESCRIPTION,255),'') as DETAILS
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.AUDITTYPECODE = 1 and LIA.FINANCIALTRANSACTIONID = @REVENUEID
and LIA.TYPECODE = 3
union all
select
LI.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LI.ADDEDBYID
,LI.DATEADDED
,-1 as AUDITTYPECODE
,LI.QUANTITY
,LI.UNITVALUE
,isnull(Left(LI.DESCRIPTION,255),'') as DETAILS
from FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.REVENUEBENEFIT_EXT RBE on LI.ID = RBE.ID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and LI.REVERSEDLINEITEMID is null
and LI.ID not in (select LIA.AUDITRECORDID
from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.AUDITTYPECODE = 0 and LIA.TYPECODE = 3)
union all
select
AUDITRECORDID
,AUDITKEY
,AUDITCHANGEAGENTID
,AUDITDATE
,AUDITTYPECODE
,QUANTITY
,UNITVALUE
,DETAILS
from (
select
LIA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,LIA.QUANTITY
,LIA.UNITVALUE
,isnull(Left(LIA.DESCRIPTION,255),'') as DETAILS
,ROW_NUMBER() over (partition by LIA.AUDITRECORDID order by LIA.AUDITDATE asc) SEQUENCE
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.AUDITTYPECODE = 0 and LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.TYPECODE = 3
and LIA.REVERSEDLINEITEMID is null) 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.QUANTITY
,LIA.UNITVALUE
,isnull(Left(LIA.DESCRIPTION,255),'') as DETAILS
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.AUDITTYPECODE = 2 and LIA.TYPECODE = 3
and LIA.REVERSEDLINEITEMID is null
and LIA.AUDITRECORDID not in (
select LI.ID from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUEBENEFIT_EXT RBE on RBE.ID = LI.ID where LI.FINANCIALTRANSACTIONID = @REVENUEID
union all
select AUDITRECORDID from FINANCIALTRANSACTIONLINEITEMAUDIT where FINANCIALTRANSACTIONLINEITEMAUDIT.AUDITTYPECODE != 2 and FINANCIALTRANSACTIONLINEITEMAUDIT.FINANCIALTRANSACTIONID = @REVENUEID and FINANCIALTRANSACTIONLINEITEMAUDIT.TYPECODE = 3)
) V1
full outer join
(select
LIA.AUDITRECORDID
,LIA.AUDITKEY
,LIA.AUDITCHANGEAGENTID
,LIA.AUDITDATE
,LIA.AUDITTYPECODE
,LIA.QUANTITY
,LIA.UNITVALUE
,isnull(Left(LIA.DESCRIPTION,255),'') as DETAILS
from FINANCIALTRANSACTIONLINEITEMAUDIT LIA
where LIA.AUDITTYPECODE in (0,2) and LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.TYPECODE = 3
and LIA.REVERSEDLINEITEMID is null) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
where
(V1.QUANTITY != V2.QUANTITY
or (V1.QUANTITY is null and V2.QUANTITY is not null)
or (V1.QUANTITY is not null and V2.QUANTITY is null)
)
or (V1.UNITVALUE != V2.UNITVALUE
or (V1.UNITVALUE is null and V2.UNITVALUE is not null)
or (V1.UNITVALUE is not null and V2.UNITVALUE is null)
)
or (V1.DETAILS != V2.DETAILS
or (V1.DETAILS is null and V2.DETAILS is not null)
or (V1.DETAILS is not null and V2.DETAILS is null)
)
) V3
cross join (
select 1 as NUM union select 2 union select 3) 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
,40 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 'Insert' end as [ACTION]
,'Benefit for:' + case NUMS.NUM when 1 then 'BENEFITID' when 2 then 'PERCENTAPPLICABLEAMOUNT' when 3 then 'VALUEPERCENT' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.BENEFITID1 = V3.BENEFITID2 then null else isnull(V3.BENEFITNAME2,'n/a') end end
when 2 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.PERCENTAPPLICABLEAMOUNT1 = V3.PERCENTAPPLICABLEAMOUNT2 then null else isnull(convert(varchar,V3.PERCENTAPPLICABLEAMOUNT2,1),'n/a') end end
when 3 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.VALUEPERCENT1 = V3.VALUEPERCENT2 then null else isnull(convert(varchar, V3.VALUEPERCENT2, 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.BENEFITID1 = V3.BENEFITID2 then null else isnull(V3.BENEFITNAME1,'n/a') end end
when 2 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.PERCENTAPPLICABLEAMOUNT1 = V3.PERCENTAPPLICABLEAMOUNT2 then null else isnull(convert(varchar,V3.PERCENTAPPLICABLEAMOUNT1,1),'n/a') end end
when 3 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.VALUEPERCENT1 = V3.VALUEPERCENT2 then null else isnull(convert(varchar, V3.VALUEPERCENT1, 1),'n/a') 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.BENEFITID as BENEFITID1
,V2.BENEFITID as BENEFITID2
,V1.PERCENTAPPLICABLEAMOUNT as PERCENTAPPLICABLEAMOUNT1
,V2.PERCENTAPPLICABLEAMOUNT as PERCENTAPPLICABLEAMOUNT2
,V1.VALUEPERCENT as VALUEPERCENT1
,V2.VALUEPERCENT as VALUEPERCENT2
,BENEFIT1.NAME as BENEFITNAME1
,BENEFIT2.NAME as BENEFITNAME2
from
(select
RBA.AUDITRECORDID
,RBA.AUDITKEY
,RBA.AUDITCHANGEAGENTID
,RBA.DATECHANGED as AUDITDATE
,RBA.AUDITTYPECODE
,RBA.BENEFITID
,RBA.PERCENTAPPLICABLEAMOUNT
,RBA.VALUEPERCENT
from dbo.REVENUEBENEFIT_EXTAUDIT RBA
inner join (select distinct T.ID from (
select LI.ID from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.REVERSEDLINEITEMID is null
union all
select LIA.AUDITRECORDID [ID] from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.REVERSEDLINEITEMID is null) T) LI on LI.ID = RBA.AUDITRECORDID
where RBA.AUDITTYPECODE = 1
union all
select
RBE.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RBE.ADDEDBYID
,RBE.DATEADDED
,-1 as AUDITTYPECODE
,RBE.BENEFITID
,RBE.PERCENTAPPLICABLEAMOUNT
,RBE.VALUEPERCENT
from FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.REVENUEBENEFIT_EXT RBE on LI.ID = RBE.ID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and LI.REVERSEDLINEITEMID is null
and LI.ID not in (select RBA.AUDITRECORDID
from dbo.REVENUEBENEFIT_EXTAUDIT RBA where RBA.AUDITTYPECODE = 0)
union all
select
AUDITRECORDID
,AUDITKEY
,AUDITCHANGEAGENTID
,AUDITDATE
,AUDITTYPECODE
,BENEFITID
,PERCENTAPPLICABLEAMOUNT
,VALUEPERCENT
from (
select
RBA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RBA.AUDITCHANGEAGENTID
,RBA.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,RBA.BENEFITID
,RBA.PERCENTAPPLICABLEAMOUNT
,RBA.VALUEPERCENT
,ROW_NUMBER() over (partition by RBA.AUDITRECORDID order by RBA.AUDITDATE asc) SEQUENCE
from REVENUEBENEFIT_EXTAUDIT RBA
inner join (select distinct T.ID from (
select LI.ID from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.REVERSEDLINEITEMID is null
union all
select LIA.AUDITRECORDID [ID] from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.REVERSEDLINEITEMID is null) T) LI on LI.ID = RBA.AUDITRECORDID
where RBA.AUDITTYPECODE = 0) BEFOREUPDATES
where BEFOREUPDATES.SEQUENCE = 1
union all
select
RBA.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,RBA.AUDITCHANGEAGENTID
,RBA.DATEADDED [AUDITDATE]
,RBA.AUDITTYPECODE
,RBA.BENEFITID
,RBA.PERCENTAPPLICABLEAMOUNT
,RBA.VALUEPERCENT
from REVENUEBENEFIT_EXTAUDIT RBA
inner join dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA on LIA.AUDITRECORDID = RBA.AUDITRECORDID
where LIA.FINANCIALTRANSACTIONID = @REVENUEID and RBA.AUDITTYPECODE = 2
and LIA.REVERSEDLINEITEMID is null
and RBA.AUDITRECORDID not in (
select AUDITRECORDID from REVENUEBENEFIT_EXTAUDIT where REVENUEBENEFIT_EXTAUDIT.AUDITTYPECODE != 2)
) V1
full outer join
(select
RBA.AUDITRECORDID
,RBA.AUDITKEY
,RBA.AUDITCHANGEAGENTID
,RBA.AUDITDATE
,RBA.AUDITTYPECODE
,RBA.BENEFITID
,RBA.PERCENTAPPLICABLEAMOUNT
,RBA.VALUEPERCENT
from REVENUEBENEFIT_EXTAUDIT RBA
inner join (select distinct T.ID from (
select LI.ID from dbo.FINANCIALTRANSACTIONLINEITEM LI where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.REVERSEDLINEITEMID is null
union all
select LIA.AUDITRECORDID [ID] from dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LIA where LIA.FINANCIALTRANSACTIONID = @REVENUEID and LIA.REVERSEDLINEITEMID is null) T) LI on LI.ID = RBA.AUDITRECORDID
where RBA.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
left join dbo.BENEFIT as BENEFIT1 on V1.BENEFITID = BENEFIT1.ID
left join dbo.BENEFIT as BENEFIT2 on V2.BENEFITID = BENEFIT2.ID
where
(V1.BENEFITID != V2.BENEFITID
or (V1.BENEFITID is null and V2.BENEFITID is not null)
or (V1.BENEFITID is not null and V2.BENEFITID is null)
)
or (V1.PERCENTAPPLICABLEAMOUNT != V2.PERCENTAPPLICABLEAMOUNT
or (V1.PERCENTAPPLICABLEAMOUNT is null and V2.PERCENTAPPLICABLEAMOUNT is not null)
or (V1.PERCENTAPPLICABLEAMOUNT is not null and V2.PERCENTAPPLICABLEAMOUNT is null)
)
or (V1.VALUEPERCENT != V2.VALUEPERCENT
or (V1.VALUEPERCENT is null and V2.VALUEPERCENT is not null)
or (V1.VALUEPERCENT is not null and V2.VALUEPERCENT is null)
)
) V3
cross join (
select 1 as NUM union select 2 union select 3) as NUMS) V4
inner join dbo.CHANGEAGENT on V4.CHANGEAGENTID = CHANGEAGENT.ID
where OLD is not null;