USP_DATAFORMTEMPLATE_EDITLOAD_RECONCILIATIONCLOSESHIFT2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECONCILIATIONCLOSESHIFT2(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @STARTINGCASH money = null output,
    @ACTUALCASH money = null output,
    @COMMENT nvarchar(max) = null output,
    @OTHERRECEIPTS xml = null output,
    @DATE date = null output,
    @USERID uniqueidentifier = null output,
    @WORKSTATIONID uniqueidentifier = null output,
    @CASHDENOMINATIONS xml = null output,
    @COINDENOMINATIONS xml = null output,
    @CHECKNUM int = null output,
    @CHECKTOTAL money = null output,
    @CLOSEDRAWERCONFIGURATIONOPTIONCODE tinyint = null output,
    @STATUSCODE tinyint = null output
)
as
    set nocount on;

    select @CLOSEDRAWERCONFIGURATIONOPTIONCODE = CONFIGURATIONOPTIONCODE from dbo.CLOSEDRAWERCONFIGURATION;

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

    select
        @DATALOADED = 1,
        @TSLONG = TSLONG,
        @STARTINGCASH = STARTINGCASH,
        @ACTUALCASH = ACTUALCASH,
        @DATE = RECONCILIATIONDATE,
        @COMMENT = COMMENT,
        @STATUSCODE = RECONCILIATION.STATUSCODE,
        @USERID = RECONCILIATION.APPUSERID
    from dbo.RECONCILIATION
    where ID = @ID

    if @DATALOADED = 1 begin
        declare @TEMPUSERID uniqueidentifier;

        if @CLOSEDRAWERCONFIGURATIONOPTIONCODE = 0 begin  -- User
            set @TEMPUSERID = @USERID;
        end

        select @OTHERRECEIPTS = (
            select ID, PAYMENTMETHODCODE, PAYMENTMETHOD, EXPECTED, ISREFUND, TOTALAMOUNT, CREDITTYPECODEID, OTHERPAYMENTMETHODCODEID
            from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS_WITH_REFUNDDETAIL(@ID, @TEMPUSERID, @WORKSTATIONID)
            where PAYMENTMETHODCODE != 1 or ISREFUND = 1
            order by ISREFUND, PAYMENTMETHODCODE, PAYMENTMETHOD
            for xml raw('ITEM'),type,elements,root('OTHERRECEIPTS'),BINARY BASE64
        )
        option (recompile);

        set @CASHDENOMINATIONS = (
            select CDD.ID, D.ID DENOMINATIONID, D.NAME, CDD.QUANTITY, D.VALUE
            from dbo.CURRENCYDENOMINATION D
            left join dbo.CLOSEDRAWERDENOMINATION CDD on CDD.CURRENCYDENOMINATIONID = D.ID and CDD.RECONCILIATIONID = @ID
            where D.TYPECODE = 0
            order by D.VALUE desc
            for xml raw('ITEM'),type,elements,root('CASHDENOMINATIONS'),BINARY BASE64
        );

        set @COINDENOMINATIONS = (
            select CDD.ID, D.ID DENOMINATIONID, D.NAME, CDD.QUANTITY, D.VALUE
            from dbo.CURRENCYDENOMINATION D
            left join dbo.CLOSEDRAWERDENOMINATION CDD on CDD.CURRENCYDENOMINATIONID = D.ID and CDD.RECONCILIATIONID = @ID
            where D.TYPECODE = 1
            order by D.VALUE desc
            for xml raw('ITEM'),type,elements,root('COINDENOMINATIONS'),BINARY BASE64
        );

        select
            @CHECKNUM = QUANTITY,
            @CHECKTOTAL = AMOUNT
        from dbo.RECONCILIATIONDETAIL
        where RECONCILIATIONID = @ID and PAYMENTMETHODCODE = 1
    end

    return 0;