USP_DATAFORMTEMPLATE_EDITLOAD_DISCOUNTGLDISTRIBUTION

The load procedure used by the edit dataform template "Discount GL Distribution Edit Form"

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.
@GLDISTRIBUTION	xml	INOUT	GL distribution
@AMOUNT	money	INOUT	Amount
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DISCOUNTGLDISTRIBUTION
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @GLDISTRIBUTION xml = null output,
    @AMOUNT money = null output,
    @TSLONG bigint = 0 output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @TSLONG = 0;

    select @GLDISTRIBUTION = dbo.UFN_REVENUE_GETCREDITGLDISTRIBUTION_TOITEMLISTXML(@ID)

    select
        @AMOUNT = sum(TRANSACTIONAMOUNT)
    from (
        -- Separating these queries to prevent scans from happening.
        -- If you modify this code, make sure to check the execution plans.
        select LI.ID, LI.TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTION as FT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
        inner join dbo.SALESORDER on SALESORDER.REVENUEID = FT.ID
        where
            LI.TYPECODE = 5  -- Discount
            and FT.TYPECODE in (5, 99)  -- Discount, Orphaned
            and SALESORDER.REVENUEID = @ID

        union

        select LI.ID, LI.TRANSACTIONAMOUNT
        from dbo.FINANCIALTRANSACTION as FT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        inner join dbo.SALESORDER on SALESORDER.ID = EXT.ID
        where
            LI.TYPECODE = 5  -- Discount
            and FT.TYPECODE in (5, 99)  -- Discount, Orphaned
            and SALESORDER.REVENUEID = @ID
    ) as DISCOUNTLINEITEMS

    if @AMOUNT is not null begin                          
        select
            @DATALOADED = 1,
            @TSLONG = max(JEXT.TSLONG)
        from dbo.JOURNALENTRY_EXT as JEXT
        inner join dbo.JOURNALENTRY as JE on JE.ID = JEXT.ID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID and LI.TYPECODE = 5  -- Discount
        inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID and FT.TYPECODE = 5  -- Discount
        where FT.ID = @ID
    end

    return 0;