USP_LOAD_BENEFIT_JOURNALENTRIES

create procedure dbo.USP_LOAD_BENEFIT_JOURNALENTRIES
(
    @TRANSACTIONID uniqueidentifier,
    @BENEFITTYPECODE tinyint,
    @DATALOADED bit = 0 output,
    @GLDISTRIBUTION xml = null output,
    @REVENUEAMOUNT money = null output,
    @ADJUSTMENTDATE datetime = null output,
    @ADJUSTMENTPOSTDATE datetime = null output,
    @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
    @ADJUSTMENTREASON nvarchar(300) = null output,
    @TSLONG bigint = 0 output,
    @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
    @DEFAULTGLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = null output
)
as
begin
    select  @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
    @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
    @ADJUSTMENTPOSTSTATUSCODE = 0;

    select
        @ADJUSTMENTDATE = BENEFITADJUSTMENT.DATE,
        @ADJUSTMENTPOSTDATE = BENEFITADJUSTMENT.POSTDATE,
        @ADJUSTMENTPOSTSTATUSCODE = 1,
        @ADJUSTMENTREASON = REASON,
        @ADJUSTMENTREASONCODEID = REASONCODEID
    from 
        dbo.BENEFITADJUSTMENT
    where 
        REVENUEID = @TRANSACTIONID
        and BENEFITADJUSTMENT.POSTSTATUSCODE = 1
        and BENEFITADJUSTMENT.BENEFITTYPECODE = 1

    -- Rename the TRANSACTIONAMOUNT field to AMOUNT.

    set @GLDISTRIBUTION = (
        select 
            GLDISTRIBUTION.ID, 
            GLDISTRIBUTION.TRANSACTIONTYPECODE, 
            GLDISTRIBUTION.TRANSACTIONAMOUNT AMOUNT, 
            GLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID, 
            GLDISTRIBUTION.ACCOUNT,
            GLDISTRIBUTION.PROJECT, 
            GLDISTRIBUTION.REFERENCE, 
            GLDISTRIBUTION.TRANSACTIONCURRENCYID,
            RBX.ID as REVENUEBENEFITID
        from
            dbo.FINANCIALTRANSACTIONLINEITEM LI
            inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
            cross apply dbo.UFN_FINANCIALTRANSACTIONLINEITEM_GETJOURNALENTRIES(LI.ID) GLDISTRIBUTION
        where
            LI.FINANCIALTRANSACTIONID = @TRANSACTIONID
            and RBX.BENEFITTYPECODE = @BENEFITTYPECODE
        for xml raw('ITEM'),type,elements,root('GLDISTRIBUTION'),BINARY BASE64
    );    

    select 
        @REVENUEAMOUNT = LI.TRANSACTIONAMOUNT
    from 
        dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = LI.ID
    where 
        LI.FINANCIALTRANSACTIONID = @TRANSACTIONID
        and RBX.BENEFITTYPECODE = @BENEFITTYPECODE;

    select 
        @TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
    from 
        dbo.FINANCIALTRANSACTION FT
    where 
        FT.ID = @TRANSACTIONID;


    select TOP 1
        @DEFAULTGLPAYMENTMETHODREVENUETYPEMAPPINGID = GLPAYMENTMETHODREVENUETYPEMAPPINGID
    from 
        dbo.UFN_FINANCIALTRANSACTIONLINEITEM_GETJOURNALENTRIES_FROMITEMLISTXML(@GLDISTRIBUTION);


    if @REVENUEAMOUNT is not null
    begin
        set @DATALOADED = 1
        select
            @TSLONG = max(JE.TSLONG)
        from
            dbo.FINANCIALTRANSACTIONLINEITEM LI
            inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
        where
            LI.FINANCIALTRANSACTIONID = @TRANSACTIONID
    end
end