UFN_CONSTITUENTHISTORY_ORGANIZATIONDATA_AUDIT

Returns the audit information for the organization data on a constituent record.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@CONSTITUENTDATEADDED	datetime	IN
@ACTIONTYPECODE	tinyint	IN
Definition

Copy


            create function dbo.UFN_CONSTITUENTHISTORY_ORGANIZATIONDATA_AUDIT
            (
                @CONSTITUENTID uniqueidentifier,
                @CONSTITUENTDATEADDED datetime,
                @ACTIONTYPECODE tinyint
            )
            returns @RESULTS table
            (
                RECORDID uniqueidentifier,
                AUDITDATE datetime,
                CHANGEDBYUSER nvarchar(128),
                PROCESS nvarchar(255),
                FIELD nvarchar(128),
                OLD nvarchar(4000),
                NEW nvarchar(4000),
                ACTION nvarchar(8),
                CHANGEDBYAPP nvarchar(200)
            ) as begin
                if @ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 1 
                    insert into @RESULTS
                        select 
                            RECORDID,
                            DATEADDED,
                            CHANGEDBYUSER,
                            PROCESS,
                            'Organization data:' + FIELD,
                            OLD,
                            NEW,
                            'Insert',
                            CHANGEDBYAPP
                        from dbo.UFN_AUDIT_GETINSERTS_FORTABLE_BYFIELDLIST('INDUSTRYCODEID,NUMEMPLOYEES,NUMSUBSIDIARIES,PARENTCORPID','ORGANIZATIONDATA', 'ID', @CONSTITUENTID, 1, @CONSTITUENTDATEADDED);

                insert into @RESULTS
                    select
                        @CONSTITUENTID,
                        case when [CHANGES].CHANGETYPE = 'Delete' then [CHANGES].AUDITDATE else [CHANGES].DATECHANGED end as [AUDITDATE],
                        [CHANGES].CHANGEDBYUSER,
                        (select PROCESSDISPLAYNAME from dbo.CHANGEAGENT where ID = [CHANGES].AUDITCHANGEAGENTID) as [PROCESS],
                        'Organization data:' + [DETAIL].FIELD, 
                        [DETAIL].OLD,
                        case when [CHANGES].CHANGETYPE = 'Delete' then 'n/a' else [DETAIL].NEW end,
                        case when [CHANGES].CHANGETYPE = 'Delete' then 'Delete' else 'Update' end,
                        [CHANGES].CHANGEDBYAPP
                    from dbo.UFN_AUDIT_GETRECENTCHANGES_FORTABLE('ORGANIZATIONDATA', 500, null, null, 0, 1, 1, null, null, @CONSTITUENTID) as [CHANGES]
                    cross apply dbo.UFN_AUDIT_GETDETAIL_BYFIELDLIST('ORGANIZATIONDATAAUDIT',[CHANGES].AUDITKEY,@CONSTITUENTID,'INDUSTRYCODEID,NUMEMPLOYEES,NUMSUBSIDIARIES,PARENTCORPID') as [DETAIL]                
                    where (([CHANGES].CHANGETYPE = 'Delete' and (@ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 3))
                        or    ([CHANGES].CHANGETYPE = 'Update' and (@ACTIONTYPECODE = 0 or @ACTIONTYPECODE = 2)));

                return;
            end