/// <summary> /// Provides back stored Loan Key data into a DataSet. /// </summary> /// <param name="loanId"></param> /// <param name="errorCode"></param> /// <param name="errorText"></param> /// <returns></returns> public static bool GetPDLoanEventDetails( PDLoan pdLoan, out string errorCode, out string errorText) { //Set default output values errorCode = string.Empty; errorText = string.Empty; if (pdLoan == null) { errorCode = "NullLoanNumber"; errorText = "The PDLoan object is null."; return(false); } DataSet outputDataSet = null; //Verify that the accessor is valid if (GlobalDataAccessor.Instance.OracleDA == null) { errorCode = "get_event_detail"; errorText = "Invalid desktop session or data accessor instance"; BasicExceptionHandler.Instance.AddException("GetPDLoanEventDetailsFailed", new ApplicationException("Cannot execute the Get PD Loan Event Details stored procedure")); return(false); } //Get data accessor object var dA = GlobalDataAccessor.Instance.OracleDA; var current_xpp = string.Empty; DateTime xpp_start_date = DateTime.Now; DateTime xpp_end_date = DateTime.Now; int xpp_fee_amt = 0; //Create input list var inParams = new List <OracleProcParam>(); inParams.Add(new OracleProcParam("p_loannumber", pdLoan.PDLLoanNumber)); //Setup ref cursor array var refCursors = new List <PairType <string, string> > { new PairType <string, string>("o_loan_event", PDLOAN_EVENT_DETAILS), }; //Create output data set names bool retVal = false; try { retVal = dA.issueSqlStoredProcCommand( "ccsowner", "PAWN_SUPPORT_PRODUCTS", "get_event_detail", inParams, refCursors, "o_return_code", "o_return_text", out outputDataSet); } catch (OracleException oEx) { errorCode = "GetPDLoanEventDetails"; errorText = "Invocation of GetPDLoanEventDetails stored proc failed"; BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking get_loan_detail stored proc", oEx); return(false); } //See if retVal is false if (retVal == false) { errorCode = dA.ErrorCode; errorText = dA.ErrorDescription; return(false); } if (outputDataSet != null && outputDataSet.IsInitialized) { if (outputDataSet.Tables != null && outputDataSet.Tables.Count > 0) { DataTable dT = outputDataSet.Tables[PDLOAN_EVENT_DETAILS]; foreach (DataRow curDr in dT.Rows) { var loanHistory = new PDLoanHistoryList(); loanHistory.Date = Utilities.GetDateTimeValue(curDr["event_date"]); loanHistory.EventType = Utilities.GetStringValue(curDr["event_type"]); loanHistory.Details = Utilities.GetStringValue(curDr["event_detail"]); loanHistory.Amount = Utilities.GetDecimalValue(curDr["amount"]); loanHistory.Source = Utilities.GetStringValue(curDr["event_source"]); loanHistory.Receipt = Utilities.GetStringValue(curDr["receipt_nbr"]); pdLoan.GetPDLoanHistoryList.Add(loanHistory); } } errorCode = "0"; errorText = string.Empty; return(true); } errorCode = "GetPDLoanEventDetailsFailed"; errorText = "Operation failed"; return(false); }
/// <summary> /// Provides back stored Loan Key data into a DataSet. /// </summary> /// <param name="loanId"></param> /// <param name="errorCode"></param> /// <param name="errorText"></param> /// <returns></returns> public static bool GetPDLoanDetails( PDLoan pdLoan, out string errorCode, out string errorText) { //Set default output values errorCode = string.Empty; errorText = string.Empty; if (pdLoan == null) { errorCode = "NullLoanNumber"; errorText = "The PDLoan object is null."; return(false); } DataSet outputDataSet = null; //Verify that the accessor is valid if (GlobalDataAccessor.Instance.OracleDA == null) { errorCode = "get_loan_details"; errorText = "Invalid desktop session or data accessor instance"; BasicExceptionHandler.Instance.AddException("GetPDLoanDetailsFailed", new ApplicationException("Cannot execute the Get PD Loan Details stored procedure")); return(false); } //Get data accessor object var dA = GlobalDataAccessor.Instance.OracleDA; var current_xpp = string.Empty; DateTime xpp_start_date = DateTime.Now; DateTime xpp_end_date = DateTime.Now; int xpp_fee_amt = 0; //Create input list var inParams = new List <OracleProcParam>(); inParams.Add(new OracleProcParam("p_loannumber", pdLoan.PDLLoanNumber)); inParams.Add(new OracleProcParam("o_current_xpp", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 256)); inParams.Add(new OracleProcParam("o_xpp_start_date", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 256)); inParams.Add(new OracleProcParam("o_xpp_end_date", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 256)); inParams.Add(new OracleProcParam("o_xpp_fee_amt", OracleDbType.Decimal, DBNull.Value, ParameterDirection.Output, 256)); inParams.Add(new OracleProcParam("o_current_workaround", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Output, 256)); //Setup ref cursor array var refCursors = new List <PairType <string, string> > { new PairType <string, string>("o_loan_detail", PDLOAN_DETAILS), new PairType <string, string>("o_xpp_detail", PDLOAN_XPP), new PairType <string, string>("o_loan_event", PDLOAN_event), }; //Create output data set names bool retVal = false; try { retVal = dA.issueSqlStoredProcCommand( "ccsowner", "PAWN_SUPPORT_PRODUCTS", "get_loan_detail", inParams, refCursors, "o_return_code", "o_return_text", out outputDataSet); } catch (OracleException oEx) { errorCode = "GetPDLoanDetails"; errorText = "Invocation of GetPDLoanDetails stored proc failed"; BasicExceptionHandler.Instance.AddException("OracleException thrown when invoking get_loan_detail stored proc", oEx); return(false); } //See if retVal is false if (retVal == false) { errorCode = dA.ErrorCode; errorText = dA.ErrorDescription; return(false); } if (outputDataSet != null && outputDataSet.IsInitialized) { if (outputDataSet.Tables != null && outputDataSet.Tables.Count > 0) { DataTable dT = outputDataSet.Tables[PDLOAN_DETAILS]; string XPP_Current = "NO"; //TODO: Need to remove the for loop. It is always one record -Madhu foreach (DataRow curDr in dT.Rows) { var loanDetails = new PDLoanDetails(); //loanDetails.CustomerSSN = Utilities.GetStringValue(curDr["open_closed"]); loanDetails.UWName = Utilities.GetStringValue(curDr["uw_name"]); loanDetails.LoanRequestDate = Utilities.GetDateTimeValue(curDr["requested_time"]); loanDetails.LoanAmt = Utilities.GetDecimalValue(curDr["loan_amt"]); loanDetails.LoanPayOffAmt = Utilities.GetDecimalValue(curDr["payoff_amt"]); loanDetails.ActualLoanAmt = Utilities.GetDecimalValue(curDr["actual_amt"]); loanDetails.LoanNumberOrig = Utilities.GetStringValue(curDr["orig_loan_nbr"]); loanDetails.LoanNumberPrev = Utilities.GetStringValue(curDr["prev_loan_nbr"]); //loanDetails.Status_Reason = Utilities.GetStringValue(curDr["status_reason"]); //loanDetails.LoanRolloverNotes = Utilities.GetStringValue(curDr["rollovers_rem"]); //loanDetails.LoanRollOverAmt = Utilities.GetDecimalValue(curDr["rollover_amt"]); var revokeACH = Utilities.GetStringValue(curDr["revoke_ach"]); if (revokeACH.Equals("Y") || revokeACH.Equals("YES")) { loanDetails.RevokeACH = true; } else { loanDetails.RevokeACH = false; } loanDetails.XPPAvailable = Utilities.GetStringValue(curDr["xpp_available"]); //var xppAvailable = Utilities.GetStringValue(curDr["xpp_available"]); //if (xppAvailable.Equals("Y") || xppAvailable.Equals("YES")) // loanDetails.XPPAvailable = true; //else // loanDetails.XPPAvailable = false; //loanDetails.ActualFinanceChrgAmt = Utilities.GetDecimalValue(curDr["actual_fc"]); //loanDetails.AcutalServiceChrgAmt = Utilities.GetDecimalValue(curDr["actual_sc"]); loanDetails.AccruedFinanceAmt = Utilities.GetDecimalValue(curDr["accrued_finance"]); loanDetails.LateFeeAmt = Utilities.GetDecimalValue(curDr["late_fee"]); loanDetails.NSFFeeAmt = Utilities.GetDecimalValue(curDr["nsf_fee"]); loanDetails.ACHWaitingToClear = Utilities.GetStringValue(curDr["ACH_waiting_clear"]); //loanDetails.EstRolloverAmt = Utilities.GetDecimalValue(curDr["est_rollover_amt"]); loanDetails.OrginationDate = Utilities.GetDateTimeValue(curDr["origination_date"]); loanDetails.DueDate = Utilities.GetDateTimeValue(curDr["due_date"]); loanDetails.OrigDepDate = Utilities.GetDateTimeValue(curDr["orig_dep_date"]); loanDetails.ExtendedDate = Utilities.GetDateTimeValue(curDr["extended_date"]); loanDetails.LastUpdatedBy = Utilities.GetStringValue(curDr["last_upd_by"]); loanDetails.ShopNo = Utilities.GetStringValue(curDr["shop_number"]); loanDetails.ShopName = Utilities.GetStringValue(curDr["shop_name"]); loanDetails.ShopState = Utilities.GetStringValue(curDr["shop_state"]); loanDetails.Status = Utilities.GetStringValue(curDr["status"]); var outputDt = outputDataSet.Tables["OUTPUT"]; if (outputDt != null && outputDt.IsInitialized && outputDt.Rows != null && outputDt.Rows.Count > 0) { var dr = outputDt.Rows[0]; if (dr != null && dr.ItemArray.Length > 0) { XPP_Current = dr.ItemArray.GetValue(1).ToString(); loanDetails.XPP_Current = XPP_Current; } dr = outputDt.Rows[1]; if (dr != null && dr.ItemArray.Length > 0) { loanDetails.XPP_Start_Date = dr.ItemArray.GetValue(1).ToString(); } dr = outputDt.Rows[2]; if (dr != null && dr.ItemArray.Length > 0) { loanDetails.XPP_End_Date = dr.ItemArray.GetValue(1).ToString(); } dr = outputDt.Rows[3]; if (dr != null && dr.ItemArray.Length > 0) { loanDetails.XPP_Fee_Amount = dr.ItemArray.GetValue(1).ToString(); } dr = outputDt.Rows[4]; if (dr != null && dr.ItemArray.Length > 0) { loanDetails.CurrentWorkaround = dr.ItemArray.GetValue(1).ToString(); } //loanDetails.XPPAvailable = dr.ItemArray.GetValue(1).ToString(); } //loanDetails.XPP_Start_Date = Utilities.GetStringValue(curDr["o_xpp_start_date"]); //loanDetails.XPP_End_Date = Utilities.GetStringValue(curDr["o_xpp_end_date"]); //loanDetails.XPP_Fee_Amount = Utilities.GetStringValue(curDr["o_xpp_fee_amt"]); pdLoan.GetPDLoanDetails = loanDetails; //Other Details var otherDetails = new PDLoanOtherDetails(); otherDetails.Loan_Type = Utilities.GetStringValue(curDr["loan_type"]); //otherDetails.ApprovalNumber = Utilities.GetStringValue(curDr["approval_no"]); otherDetails.ThirdPartyLoanNumber = Utilities.GetStringValue(curDr["veritec_trans_nbr"]); //otherDetails.LoanApr = Utilities.GetStringValue(curDr["loan_apr"]); //otherDetails.MultipleLoan = Utilities.GetStringValue(curDr["multiple_loan"]); //otherDetails.ApprovedFinanceChrgRate = Utilities.GetStringValue(curDr["appr_fc"]); otherDetails.RefunedServiceChrgAmt = Utilities.GetStringValue(curDr["refund_amt"]); otherDetails.CourtCostAmt = Utilities.GetStringValue(curDr["court_fees_assessed"]); otherDetails.RequestedLoanAmt = Utilities.GetStringValue(curDr["requested_amt"]); otherDetails.ApprovedLoanAmt = Utilities.GetStringValue(curDr["approved_amt"]); otherDetails.CheckNumber = Utilities.GetStringValue(curDr["check_nbr"]); //otherDetails.ActualFinanceChrg = Utilities.GetStringValue(curDr["actfc_acq"]); otherDetails.OrigMastringFee = Utilities.GetStringValue(curDr["orig_fee"]); otherDetails.ActualFinanceChrgRate = Utilities.GetStringValue(curDr["actual_fc_rate"]); otherDetails.APR = Utilities.GetStringValue(curDr["apr"]); //otherDetails.EstRefinanceAmt = Utilities.GetStringValue(curDr["est_refi_amt"]); otherDetails.StatusChgDate = Utilities.GetStringValue(curDr["status_chg_date"]); //otherDetails.CollectionStatusDesc = Utilities.GetStringValue(curDr["collection_status"]); //otherDetails.InsOnDate = Utilities.GetStringValue(curDr["ins_on"]); //otherDetails.RefisAvailable = Utilities.GetStringValue(curDr["refi_remaining"]); otherDetails.PaymentFrequency = Utilities.GetStringValue(curDr["freq_paid"]); //otherDetails.SuspendACH = Utilities.GetStringValue(curDr["auto_susp_ach"]); otherDetails.ActualBrokerAmt = Utilities.GetStringValue(curDr["actual_broker_amt"]); //otherDetails.BrokerRate = Utilities.GetStringValue(curDr["broker_rate"]); pdLoan.GetPDLoanOtherDetails = otherDetails; } DataTable dTXPP = outputDataSet.Tables[PDLOAN_XPP]; if (XPP_Current.Equals("YES") && dTXPP != null) { foreach (DataRow curDr in dTXPP.Rows) { var XPPDetail = new PDLoanXPPScheduleList(); XPPDetail.xppPaymentSeqNumber = Utilities.GetStringValue(curDr["schedule_nbr"]); XPPDetail.xppPaymentNumber = Utilities.GetStringValue(curDr["payment_nbr"]); XPPDetail.xppDate = Utilities.GetDateTimeValue(curDr["payment_date"]); XPPDetail.xppAmount = Utilities.GetDecimalValue(curDr["payment_amount"]); pdLoan.GetPDLoanXPPScheduleList.Add(XPPDetail); } } DataTable dTEvent = outputDataSet.Tables[PDLOAN_event]; if (dTEvent != null) { foreach (DataRow curDr in dTEvent.Rows) { var loanHistory = new PDLoanHistoryList(); loanHistory.Date = Utilities.GetDateTimeValue(curDr["event_date"]); loanHistory.EventType = Utilities.GetStringValue(curDr["event_type"]); loanHistory.Details = Utilities.GetStringValue(curDr["event_detail"]); loanHistory.Amount = Utilities.GetDecimalValue(curDr["amount"]); loanHistory.Source = Utilities.GetStringValue(curDr["event_source"]); loanHistory.Receipt = Utilities.GetStringValue(curDr["receipt_nbr"]); pdLoan.GetPDLoanHistorySummaryList.Add(loanHistory); } } } errorCode = "0"; errorText = string.Empty; return(true); } errorCode = "GetPDLoanDetailsFailed"; errorText = "Operation failed"; return(false); }