UFN_AUDIT_GETDETAILSFORREVENUEGLDISTRIBUTION
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_AUDIT_GETDETAILSFORREVENUEGLDISTRIBUTION](@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
--First get all of the journal entries that exist or existed for this revenue record
declare @JournalEntries table (ID uniqueidentifier, primary key(ID));
insert into @JournalEntries
select distinct T.ID from (
--Most current journal entries
select JE.ID
from dbo.JOURNALENTRY JE
inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
where LI.FINANCIALTRANSACTIONID = @REVENUEID and JEX.TABLENAMECODE = 1
union all
--Journal entries in the audit that are linked to line items that are not in the audit table
select JE.AUDITRECORDID
from dbo.JOURNALENTRYAUDIT JE
inner join (
select ID
from dbo.JOURNALENTRY_EXT JEX
where JEX.TABLENAMECODE = 1
union all
select AUDITRECORDID
from dbo.JOURNALENTRY_EXTAUDIT JEXA
where JEXA.TABLENAMECODE = 1) JEX on JEX.ID = JE.AUDITRECORDID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
union all
--Journal entries in the audit that are linked to line items that are only in the audit table
select JE.AUDITRECORDID
from dbo.JOURNALENTRYAUDIT JE
inner join (
select ID
from dbo.JOURNALENTRY_EXT JEX
where JEX.TABLENAMECODE = 1
union all
select AUDITRECORDID
from dbo.JOURNALENTRY_EXTAUDIT JEXA
where JEXA.TABLENAMECODE = 1) JEX on JEX.ID = JE.AUDITRECORDID
inner join dbo.FINANCIALTRANSACTIONLINEITEMAUDIT LI on LI.AUDITRECORDID = JE.FINANCIALTRANSACTIONLINEITEMID
where LI.FINANCIALTRANSACTIONID = @REVENUEID) 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
,50 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]
,'GL Distribution for:' + case NUMS.NUM when 1 then 'TRANSACTIONTYPECODE' when 2 then 'REFERENCE' when 3 then 'AMOUNT' when 4 then 'ACCOUNT' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.TRANSACTIONTYPECODE1 = V3.TRANSACTIONTYPECODE2 then null else isnull(V3.TRANSACTIONTYPECODE2,'n/a') end end
when 2 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.REFERENCE1 = V3.REFERENCE2 then null else ISNULL(V3.REFERENCE2, 'n/a') end end
when 3 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT2,1),'n/a') end end
when 4 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.GLACCOUNTID1 = V3.GLACCOUNTID2 then null else ISNULL(V3.ACCOUNTNUMBER2, '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.TRANSACTIONTYPECODE1 = V3.TRANSACTIONTYPECODE2 then null else isnull(V3.TRANSACTIONTYPECODE1,'n/a') end end
when 2 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.REFERENCE1 = V3.REFERENCE2 then null else ISNULL(V3.REFERENCE1, 'n/a') end end
when 3 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.AMOUNT1 = V3.AMOUNT2 then null else isnull(convert(varchar,V3.AMOUNT1,1),'n/a') end end
when 4 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.GLACCOUNTID1 = V3.GLACCOUNTID2 then null else ISNULL(V3.ACCOUNTNUMBER1, '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.TRANSACTIONTYPECODE as TRANSACTIONTYPECODE1
,V2.TRANSACTIONTYPECODE as TRANSACTIONTYPECODE2
,V1.COMMENT as REFERENCE1
,V2.COMMENT as REFERENCE2
,V1.BASEAMOUNT as AMOUNT1
,V2.BASEAMOUNT as AMOUNT2
,V1.GLACCOUNTID as GLACCOUNTID1
,V2.GLACCOUNTID as GLACCOUNTID2
,GLACCOUNT1.ACCOUNTNUMBER as ACCOUNTNUMBER1
,GLACCOUNT2.ACCOUNTNUMBER as ACCOUNTNUMBER2
from
(--First get the inserted data from the base table if no audits exists for the record.
select
JE.AUDITRECORDID
,JE.AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.DATECHANGED as AUDITDATE
,JE.AUDITTYPECODE
,case JE.TRANSACTIONTYPECODE when 0 then N'Debit' when 1 then N'Credit' end TRANSACTIONTYPECODE
,JE.COMMENT
,JE.BASEAMOUNT
,JE.GLACCOUNTID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 1
union all
select
JE.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JE.ADDEDBYID
,JE.DATEADDED
,-1 as AUDITTYPECODE
,case JE.TRANSACTIONTYPECODE when 0 then N'Debit' when 1 then N'Credit' end TRANSACTIONTYPECODE
,JE.COMMENT
,JE.BASEAMOUNT
,JE.GLACCOUNTID
from dbo.JOURNALENTRY JE
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and JE.ID not in (select JE.AUDITRECORDID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 0)
union all
select
AUDITRECORDID
,AUDITKEY
,AUDITCHANGEAGENTID
,AUDITDATE
,AUDITTYPECODE
,TRANSACTIONTYPECODE
,COMMENT
,BASEAMOUNT
,GLACCOUNTID
from (--Get inserted data from the first 'Before update" entry in the audit table
select
JE.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,case JE.TRANSACTIONTYPECODE when 0 then N'Debit' when 1 then N'Credit' end TRANSACTIONTYPECODE
,JE.COMMENT
,JE.BASEAMOUNT
,JE.GLACCOUNTID
,ROW_NUMBER() over (partition by JE.AUDITRECORDID order by JE.AUDITDATE asc) SEQUENCE
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 0) BEFOREUPDATES
where BEFOREUPDATES.SEQUENCE = 1
union all
--Get inserted data from the "Before delete" entry in the audit table
select
JE.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JE.ADDEDBYID
,JE.DATEADDED [AUDITDATE]
,JE.AUDITTYPECODE
,case JE.TRANSACTIONTYPECODE when 0 then N'Debit' when 1 then N'Credit' end TRANSACTIONTYPECODE
,JE.COMMENT
,JE.BASEAMOUNT
,JE.GLACCOUNTID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 2 and JE.AUDITRECORDID not in (
select JE.ID from dbo.JOURNALENTRY JE inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID where LI.FINANCIALTRANSACTIONID = @REVENUEID
union all
select AUDITRECORDID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE != 2)
) V1
full outer join
(
select
JE.AUDITRECORDID
,JE.AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.AUDITDATE
,JE.AUDITTYPECODE
,case JE.TRANSACTIONTYPECODE when 0 then N'Debit' when 1 then N'Credit' end TRANSACTIONTYPECODE
,JE.COMMENT
,JE.BASEAMOUNT
,JE.GLACCOUNTID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
left join dbo.GLACCOUNT GLACCOUNT1 on GLACCOUNT1.ID = V1.GLACCOUNTID
left join dbo.GLACCOUNT GLACCOUNT2 on GLACCOUNT2.ID = V2.GLACCOUNTID
where
(V1.TRANSACTIONTYPECODE != V2.TRANSACTIONTYPECODE
or (V1.TRANSACTIONTYPECODE is null and V2.TRANSACTIONTYPECODE is not null)
or (V1.TRANSACTIONTYPECODE is not null and V2.TRANSACTIONTYPECODE is null)
)
or (V1.COMMENT != V2.COMMENT
or (V1.COMMENT is null and V2.COMMENT is not null)
or (V1.COMMENT is not null and V2.COMMENT is null)
)
or (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)
)
or (V1.GLACCOUNTID != V2.GLACCOUNTID
or (V1.GLACCOUNTID is null and V2.GLACCOUNTID is not null)
or (V1.GLACCOUNTID is not null and V2.GLACCOUNTID is null)
)
) V3
cross join (
select 1 as NUM union select 2 union select 3 union select 4) 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
,50 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]
,'GL Distribution for:' + case NUMS.NUM when 1 then 'GLPAYMENTMETHODREVENUETYPEMAPPINGID' when 2 then 'PROJECT' end as FIELD
,case NUMS.NUM
when 1 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.GLPAYMENTMETHODREVENUETYPEMAPPINGID1 = V3.GLPAYMENTMETHODREVENUETYPEMAPPINGID2 then null else isnull(V3.GLPAYMENTMETHODREVENUETYPEMAPPING2,'n/a') end end
when 2 then case V3.AUDITTYPECODE1 when -1 then 'n/a' else case when V3.PROJECT1 = V3.PROJECT2 then null else isnull(V3.PROJECT2,'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.GLPAYMENTMETHODREVENUETYPEMAPPINGID1 = V3.GLPAYMENTMETHODREVENUETYPEMAPPINGID2 then null else isnull(V3.GLPAYMENTMETHODREVENUETYPEMAPPING1,'n/a') end end
when 2 then case V3.AUDITTYPECODE2 when 2 then 'n/a' else case when V3.PROJECT1 = V3.PROJECT2 then null else isnull(V3.PROJECT1,'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.GLPAYMENTMETHODREVENUETYPEMAPPINGID as GLPAYMENTMETHODREVENUETYPEMAPPINGID1
,V2.GLPAYMENTMETHODREVENUETYPEMAPPINGID as GLPAYMENTMETHODREVENUETYPEMAPPINGID2
,V1.PROJECT as PROJECT1
,V2.PROJECT as PROJECT2
,GLPAYMENTMETHODREVENUETYPEMAPPING.DESCRIPTION as GLPAYMENTMETHODREVENUETYPEMAPPING1
,GLPAYMENTMETHODREVENUETYPEMAPPING2.DESCRIPTION as GLPAYMENTMETHODREVENUETYPEMAPPING2
from
(select
JE.AUDITRECORDID
,JE.AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.DATECHANGED as AUDITDATE
,JE.AUDITTYPECODE
,JE.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JE.PROJECT
from JOURNALENTRY_EXTAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 1
union all
select
JEX.ID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JEX.ADDEDBYID
,JEX.DATEADDED
,-1 as AUDITTYPECODE
,JEX.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JEX.PROJECT
from dbo.JOURNALENTRY_EXT JEX
inner join dbo.JOURNALENTRY JE on JE.ID = JEX.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
where LI.FINANCIALTRANSACTIONID = @REVENUEID
and JE.ID not in (select JE.AUDITRECORDID
from JOURNALENTRY_EXTAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 0)
union all
select
AUDITRECORDID
,AUDITKEY
,AUDITCHANGEAGENTID
,AUDITDATE
,AUDITTYPECODE
,GLPAYMENTMETHODREVENUETYPEMAPPINGID
,PROJECT
from (
select
JE.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.DATEADDED [AUDITDATE]
,-1 as AUDITTYPECODE
,JE.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JE.PROJECT
,ROW_NUMBER() over (partition by JE.AUDITRECORDID order by JE.AUDITDATE asc) SEQUENCE
from JOURNALENTRY_EXTAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 0) BEFOREUPDATES
where BEFOREUPDATES.SEQUENCE = 1
union all
select
JE.AUDITRECORDID
,'00000000-0000-0000-0000-000000000000' as AUDITKEY
,JE.ADDEDBYID
,JE.DATEADDED [AUDITDATE]
,JE.AUDITTYPECODE
,JE.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JE.PROJECT
from JOURNALENTRY_EXTAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE = 2 and JE.AUDITRECORDID not in (
select JE.ID from dbo.JOURNALENTRY JE inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID where LI.FINANCIALTRANSACTIONID = @REVENUEID
union all
select AUDITRECORDID
from JOURNALENTRYAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE != 2)
) V1
full outer join
(select
JE.AUDITRECORDID
,JE.AUDITKEY
,JE.AUDITCHANGEAGENTID
,JE.AUDITDATE
,JE.AUDITTYPECODE
,JE.GLPAYMENTMETHODREVENUETYPEMAPPINGID
,JE.PROJECT
from JOURNALENTRY_EXTAUDIT JE
inner join @JournalEntries LI on LI.ID = JE.AUDITRECORDID
where JE.AUDITTYPECODE in (0,2)) V2 on V1.AUDITRECORDID = V2.AUDITRECORDID and V1.AUDITKEY = V2.AUDITKEY
left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING on V1.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING.ID
left join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as GLPAYMENTMETHODREVENUETYPEMAPPING2 on V2.GLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPING2.ID
where
(V1.GLPAYMENTMETHODREVENUETYPEMAPPINGID != V2.GLPAYMENTMETHODREVENUETYPEMAPPINGID
or (V1.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null and V2.GLPAYMENTMETHODREVENUETYPEMAPPINGID is not null)
or (V1.GLPAYMENTMETHODREVENUETYPEMAPPINGID is not null and V2.GLPAYMENTMETHODREVENUETYPEMAPPINGID is null)
)
or (V1.PROJECT != V2.PROJECT
or (V1.PROJECT is null and V2.PROJECT is not null)
or (V1.PROJECT is not null and V2.PROJECT is null)
)
) V3
cross join (
select 1 as NUM union select 2) as NUMS) V4
inner join dbo.CHANGEAGENT on V4.CHANGEAGENTID = CHANGEAGENT.ID
where OLD is not null;
return;
end