USP_DATAFORMTEMPLATE_VIEW_UNMAPPEDTRANSACTIONERROR

The load procedure used by the view dataform template "Unmapped Transaction Detail"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TRANSACTIONTYPE	nvarchar(100)	INOUT	Transaction type
@OCCURRENCES	int	INOUT	Occurrences
@ERRORMESSAGE	nvarchar(max)	INOUT	Message
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT	Account system
@ADDRESSED	bit	INOUT	Reviewed

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_UNMAPPEDTRANSACTIONERROR
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TRANSACTIONTYPE nvarchar(100) = null output,
    @OCCURRENCES int = null output,
    @ERRORMESSAGE nvarchar(max) = null output,
    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
    @ADDRESSED bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    select @DATALOADED = 1
        ,@PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
        ,@ERRORMESSAGE = ME.ERRORMESSAGE
        ,@ADDRESSED = ME.ADDRESSED
    from dbo.GLACCOUNTMAPPINGERROR ME
    left join dbo.SALESORDER SO on SO.ID = ME.TRANSACTIONID
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = ME.TRANSACTIONID or FT.ID = SO.REVENUEID
    where ME.ID = @ID;

    WITH ErrorMessage_CTE (TRANSACTIONTYPE, OCCURRENCES)
    AS 
    (
        select ME.TRANSACTIONTYPE, COUNT(ME.ID)
        from dbo.GLACCOUNTMAPPINGERROR ME
        left join dbo.SALESORDER SO on SO.ID = ME.TRANSACTIONID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = ME.TRANSACTIONID or FT.ID = SO.REVENUEID
        where FT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
            and ME.ERRORMESSAGE = @ERRORMESSAGE
            and ME.DELETED = 0
            and ME.ADDRESSED = @ADDRESSED
        group by ME.TRANSACTIONTYPE
    )

    select @TRANSACTIONTYPE = case when (select COUNT(TRANSACTIONTYPE) from ErrorMessage_CTE) > 1 THEN 'Multiple' ELSE MAX(ME1.TRANSACTIONTYPE) END
        ,@OCCURRENCES = SUM(ME1.OCCURRENCES)
    from ErrorMessage_CTE ME1

    return 0;