UFN_RECONCILIATION_GETOTHERRECEIPTS

            CREATE function dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS
            (
                @RECONCILIATIONID uniqueidentifier,
                @APPUSERID uniqueidentifier = null
            ) returns table 
            as
            return
                select
                    isnull(EXPECTED.ID, ACTUAL.ID) as ID,
                    isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE) as PAYMENTMETHODCODE,
                    case when isnull(EXPECTED.PAYMENTMETHODCODE, ACTUAL.PAYMENTMETHODCODE) in (0,1) then coalesce(EXPECTED.PAYMENTMETHOD, ACTUAL.PAYMENTMETHOD) end [PAYMENTMETHOD],
                    isnull(EXPECTED.CREDITTYPECODEID, ACTUAL.CREDITTYPECODEID) as CREDITTYPECODEID,
                    isnull(EXPECTED.OTHERPAYMENTMETHODCODEID, ACTUAL.OTHERPAYMENTMETHODCODEID) as OTHERPAYMENTMETHODCODEID,
                    ACTUAL.QUANTITY as QUANTITY,
                    EXPECTED.EXPECTED as EXPECTED,
                    isnull(EXPECTED.ISREFUND, ACTUAL.ISREFUND) ISREFUND,
                    cast(0 as bit) as ISLABEL,
                    EXPECTED.TOTALAMOUNT
                from 
                (
                    select
                        ID,
                        PAYMENTMETHODCODE,
                        PAYMENTMETHOD,
                        CREDITTYPECODEID,
                        OTHERPAYMENTMETHODCODEID,
                        count(1) as EXPECTED,
                        ISREFUND as ISREFUND,
                        sum(TOTALAMOUNT) as TOTALAMOUNT
                    from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS_FULL(@RECONCILIATIONID, @APPUSERID, null)
                    group by
                        PAYMENTMETHODCODE,
                        ISREFUND,
                        PAYMENTMETHOD,
                        CREDITTYPECODEID,
                        OTHERPAYMENTMETHODCODEID,
                        ID
                ) as EXPECTED
                full outer join
                (
                    select
                        ID,
                        PAYMENTMETHODCODE,
                        PAYMENTMETHOD,
                        CREDITTYPECODEID,
                        OTHERPAYMENTMETHODCODEID,
                        QUANTITY,
                        0 AS [TOTALAMOUNT],
                        0 as ISREFUND
                    from dbo.RECONCILIATIONDETAIL RD
                    where RECONCILIATIONID = @RECONCILIATIONID

                    union all

                    select
                        ID
                        ,null PAYMENTMETHODCODE
                        ,null PAYMENTMETHOD
                        ,null CREDITTYPECODEID
                        ,null OTHERPAYMENTMETHODCODEID
                        ,QUANTITY
                        ,0 AS [TOTALAMOUNT]
                        ,1 as ISREFUND
                    from dbo.RECONCILIATIONCREDITDETAIL RD
                    where RD.ID = @RECONCILIATIONID
                ) as ACTUAL
                    on EXPECTED.ID = ACTUAL.ID 
                    and ((EXPECTED.PAYMENTMETHODCODE = ACTUAL.PAYMENTMETHODCODE
                    and (
                        EXPECTED.PAYMENTMETHODCODE = 1 or
                        (EXPECTED.PAYMENTMETHODCODE = 2 and EXPECTED.CREDITTYPECODEID = ACTUAL.CREDITTYPECODEID) or
                        (EXPECTED.PAYMENTMETHODCODE = 10 and EXPECTED.OTHERPAYMENTMETHODCODEID = ACTUAL.OTHERPAYMENTMETHODCODEID)
                    ))
                    or (EXPECTED.PAYMENTMETHODCODE is null and ACTUAL.PAYMENTMETHODCODE is null))