USP_DATAFORMTEMPLATE_EDITLOAD_CREDITGLDISTRIBUTION

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

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    set @GLDISTRIBUTION = (
        select
            JE.ID,
            JE.TRANSACTIONAMOUNT AMOUNT,
            JE.TRANSACTIONTYPECODE,
            JE.GLACCOUNTID,
            JEX.PROJECT,
            JE.COMMENT as REFERENCE,
            JE.TRANSACTIONCURRENCYID,
            FT.PDACCOUNTSYSTEMID
        from
            dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
            inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
        where FT.ID = @ID
        for xml raw('ITEM'),type,elements,root('GLDISTRIBUTION'),BINARY BASE64
    );

    if @GLDISTRIBUTION is not null
    begin
        select
            @TSLONG = max(JE.TSLONG),
            @DATALOADED = 1
        from
            dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
        where FT.ID = @ID
    end
    return 0;
end