var query = from i in GetInternal(db, masterId, nowDate,
                            c => PendingStates.Contains(c.status) && ReturnPremiumTypes.Contains(c.type))
                            where
                                !i.HasFlag(ReturnPremiumTabFlags.MarkAsProcessed)
                                && !i.HasFlag(ReturnPremiumTabFlags.Suspended)
                                && (
                                    i.HasFlag(ReturnPremiumTabFlags.ForcePending)
                                    || (!(i.HasFlag(ReturnPremiumTabFlags.EnHolding)
                                            || i.HasFlag(ReturnPremiumTabFlags.CancelHolding))
                                        && !i.HasFlag(ReturnPremiumTabFlags.CarryForward)
                                        && i.HasFlag(ReturnPremiumTabFlags.Pending))
                                )
                            orderby i.PolicyExpirationDate, i.PolicyNumber, i.PaymentDueDate
                            select i;
                return query.ToList();

        [ExpressionMethod("HasFlagExpression")]
        public static bool HasFlag(this ReturnPremiumViewItem item, ReturnPremiumTabFlags flag) {
            throw new NotSupportedException();
        }

        public static Expression HasFlagExpression() {
            return
                (Expression<Func<ReturnPremiumViewItem, ReturnPremiumTabFlags, bool>>)
                ((t, flag) => (t.TabFlags & flag) > 0);
        }

// and internal query is

            var queryZero =
                from c in cashQuery
                join cb in db.t_ws_cash_txn_book_txn on c.id equals cb.cash_txn_id
                join b in db.t_ws_book_txn on cb.book_txn_id equals b.book_txn_id
                join bc in db.t_ws_billing_cycle on b.billing_cycle_id equals bc.billing_cycle_id
                join bp in db.t_bd_base_policy on bc.base_policy_id equals bp.base_policy_id
                join a in db.t_ws_account on bp.account_id equals a.account_id
                join i in db.t_bd_insured on a.insured_id equals i.insured_id
                join crp in db.t_bd_carrier_programs on bc.carrier_program_id equals crp.id
                join cr in db.t_bd_carrier on crp.carrier_id equals cr.carrier_id
                join ga in db.t_bd_ga on crp.ga_id equals ga.master_id
                from paymentInstrument in db.t_bd_payment_instrument
                    .Where(q => q.payment_instrument_id == c.payment_instrument_id)
                    .Select(q => new PaymentInstrument() {
                        Id = q.payment_instrument_id,
                        Type = q.payment_instrument_tp_id,
                        EssentialsId = q.payment_essentials_id,
                        State = q.state
                    })
                    .DefaultIfEmpty()
                from payee in db.t_ws_cash_txn_payee
                    .Where(q => q.cash_txn_id == c.id)
                    .Select(q => new { q.payee_type })
                    .DefaultIfEmpty()
                where
                    ga.master_id == masterId
                //orderby bc.expiration_date, bc.policy_no, c.due_date
                select new {
                    cash = c,
                    cash2book = cb,
                    book = b,
                    cycle = bc,
                    policy = bp,
                    account = a,
                    insured = i,
                    program = crp,
                    carrier = cr,
                    ga = ga,
                    pay_inst = paymentInstrument,
                    payee = payee
                };


            var cf_threshold_query = GetSettingsByNameQuery(db, ProgramSettingsConsts.CARRY_FORWARD_THRESHOLD);

            var rp_holding_threshold_query = GetSettingsByNameQuery(db, ProgramSettingsConsts.RP_HOLDING_THRESHOLD);

            var pending_rp_amount_threshold_query = GetSettingsByNameQuery(db, ProgramSettingsConsts.PENDING_RP_AMOUNT_THRESHOLD);


            var query =
                from f in queryZero
                from th in cf_threshold_query
                    .Where(t => t.carrier_program_id == f.program.id)
                    .Select(t => t.value)
                    .Distinct()
                    .Where(t => ISNUMERIC(t) == 1)
                    .Select(t => new { exp_threshold = Sql2.ConvertTo<int>.From(t) })
                    .Distinct()
                    .Take(1)
                    .DefaultIfEmpty()
                from thrp in rp_holding_threshold_query
                    .Where(t => t.carrier_program_id == f.program.id)
                    .Select(t => t.value)
                    .Distinct()
                    .Where(t => ISNUMERIC(t) == 1)
                    .Select(t => new { rp_threshold = Sql2.ConvertTo<int>.From(t) })
                    .Distinct()
                    .Take(1)
                    .DefaultIfEmpty()
                from prath in pending_rp_amount_threshold_query
                    .Where(t => t.carrier_program_id == f.program.id)
                    .Select(t => t.value)
                    .Distinct()
                    .Where(t => ISNUMERIC(t) == 1)
                    .Select(t => new { pr_amont_threshold = Sql2.Abs(Sql2.ConvertTo<int>.From(t)) })
                    .Distinct()
                    .Take(1)
                    .DefaultIfEmpty()
                let p_t = f.pay_inst != null
                        ? f.pay_inst.Type
                        : (((f.payee != null ? f.payee.payee_type : Payee.Producer) == Payee.Producer)
                            ? PaymentInstrumentType.AgencyEft
                            : PaymentInstrumentType.GACheck)
                let diffNowUntilExpirationDate =
                    Sql2.AsSql(Sql2.DateDiff(Sql2.DateParts.Day, now, f.cycle.expiration_date))
                let diffNowUntilDue =
                    Sql2.AsSql(Sql2.DateDiff(Sql2.DateParts.Day, now, f.cash.due_date))
                let is_pending = diffNowUntilDue <= 0 // now >= cash due date
                let is_force_pending = (f.cash.settings & CashTransactionSettings.DisplayAsPendingInReturnPremiumPage) > 0
                let is_force_processed = (f.cash.settings & CashTransactionSettings.MarkForProcessedInReturnPremiumPage) > 0
                let is_carry_forward = CarryForwardTypes.Contains(f.cash.type)
                    && (th != null && diffNowUntilExpirationDate >= 0
                        && diffNowUntilExpirationDate < th.exp_threshold)
                let is_force_carry_forward = (f.cash.settings & CashTransactionSettings.CarryForwardToRenewal) > 0
                let is_force_carry_forward2 = (prath == null || Sql2.Abs(f.cash.payment_amt) <= prath.pr_amont_threshold)
                let is_en_holding =
                    CarryForwardTypes.Contains(f.cash.type)
                    && thrp != null
                    && ((is_carry_forward && diffNowUntilExpirationDate < 0
                            && Sql2.Abs(diffNowUntilExpirationDate) < thrp.rp_threshold)
                        || (!is_carry_forward
                        && (diffNowUntilDue > 0)
                        /*&& diffNowUntilDue <= 0 && Sql.Abs(diffNowUntilDue) < thrp.rp_threshold*/))
                    && (prath == null || Sql2.Abs(f.cash.payment_amt) > prath.pr_amont_threshold)
                let is_cn_holding =
                    (f.cash.type == CashTransactionType.CancellationReturnPremium
                        || f.cash.type == CashTransactionType.Refund)
                    && thrp != null
                    && (diffNowUntilDue > 0)
                    //&& (diffNowUntilDue <= 0 && Sql.Abs(diffNowUntilDue) < thrp.rp_threshold)
                    && (prath == null || Sql2.Abs(f.cash.payment_amt) > prath.pr_amont_threshold)
                let is_suspended = f.cash.status == CashTransactionState.Suspended
                select new ReturnPremiumViewItem {
                    Id = f.cash.id,
                    IsSuspended = f.cash.status == CashTransactionState.Suspended,
                    IsPending = f.cash.status == CashTransactionState.Pending,
                    IsProcessed = f.cash.status == CashTransactionState.Processed,
                    IsCleared = f.cash.status == CashTransactionState.Cleared,
                    TransactionDate = f.cash.date_started,
                    PolicyNumber = f.cycle.policy_no,
                    ProducerCode = f.cycle.producer_cd,
                    Producer = new Producer {
                        ProducerCode = f.cycle.producer_cd,
                        PCProducerAppointmentId = f.cycle.pc_appointment_id
                    },
                    Type = f.cash.type,
                    State = f.cash.status,
                    PaymentDueDate = f.cash.due_date,
                    SettledDate = f.cash.date_settled,
                    ClearedDate = f.cash.date_cleared,
                    RpAmount = f.cash.payment_amt,
                    PaymentInstrument = f.pay_inst,
                    PaymentInstrumentType = p_t,
                    Payee = f.payee != null ? f.payee.payee_type : Payee.Producer,
                    IsPay = (f.cash.settings & CashTransactionSettings.DisplayAsPendingInReturnPremiumPage) > 0,
                    SuspensionReason = f.cash.suspense_reason.ToString(),
                    SuspensionNotes = f.cash.suspense_notes,
                    SettlementDetails = f.cash.settlement_details,
                    Settings = f.cash.settings,
                    PolicyExpirationDate = f.cycle.expiration_date,
                    ProgramName = f.program.name,
                    ProgramId = f.program.id,

                    CarryForwardThresholdSetting = th.exp_threshold,
                    RpHoldingThresholdSetting = thrp.rp_threshold,
                    PendingRpAmountThreshold = prath.pr_amont_threshold,
                    DiffNowUntilExpirationDate = diffNowUntilExpirationDate,
                    DiffNowUntilDue = diffNowUntilDue,
                    TabFlags = Sql2.AsSql(
                              GetFlag(is_pending, ReturnPremiumTabFlags.Pending)
                            | GetFlag(is_force_pending, ReturnPremiumTabFlags.ForcePending)
                            | GetFlag(is_force_processed, ReturnPremiumTabFlags.MarkAsProcessed)
                            | GetFlag(is_carry_forward, ReturnPremiumTabFlags.CarryForward)
                            | GetFlag(is_force_carry_forward, ReturnPremiumTabFlags.CarryForward)
                            | GetFlag(is_force_carry_forward2, ReturnPremiumTabFlags.CarryForward)
                            | GetFlag(is_en_holding, ReturnPremiumTabFlags.EnHolding)
                            | GetFlag(is_cn_holding, ReturnPremiumTabFlags.CancelHolding)
                            | GetFlag(is_suspended, ReturnPremiumTabFlags.Suspended)
                            ),
                    BillingCycleId = f.cycle.billing_cycle_id,
                    MasterId = f.ga.master_id,
                    InsuredId = f.insured.insured_id,
                    InsuredName = f.insured.first_name + " " + f.insured.last_name,
                    HasMortgageePayments = Sql2.AsSql(HasMortgageePaymentsValue(db, f.cycle))

                };