Notes

private void CreateJournals()
{
var transactionWithLeaseDetails = from lease in leaseDetailsOfTransactions
join transaction in transactionsToProcessJournals
on lease.Contract.SequenceNumber equals transaction.LeaseNumber
select new { lease, transaction };
foreach (var leaseTransactionData in transactionWithLeaseDetails)
{
if (leaseTransactionData.transaction.TransactionCode == BDTTransactionTypeValues.N)
{
var GLJournalHelper = GlJournalHelper.CreateGLJournal(repository, leaseTransactionData.lease.Contract, (DateTime)leaseTransactionData.lease.LeaseFinance.LegalEntity.BusinessUnit.CurrentBusinessDate, leaseTransactionData.lease.LeaseFinance.LegalEntity.Id, leaseTransactionData.lease.LeaseFinanceDetail.InsuranceChargeBookingGLTemplateId.Value);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermPremiumChargeValue)),
GLEntryItemValues.LongTermInsurancePremiumReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermFinanceFeeValue)),
GLEntryItemValues.LongTermInsuranceFinanceChargeReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermServiceFeeValue)),
GLEntryItemValues.LongTermInsuranceServiceFeeReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermPremiumChargeValue)),
GLEntryItemValues.InsurancePremiumPayable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermFinanceFeeValue)),
GLEntryItemValues.UnearnedInsuranceFinanceCharge,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermServiceFeeValue)),
GLEntryItemValues.UnearnedInsuranceServiceFee,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Booking",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.MakeEntries();
}
else if (leaseTransactionData.transaction.TransactionCode == BDTTransactionTypeValues.C)
{
var GLJournalHelper = GlJournalHelper.CreateGLJournal(repository, leaseTransactionData.lease.Contract, (DateTime)leaseTransactionData.lease.LeaseFinance.LegalEntity.BusinessUnit.CurrentBusinessDate, leaseTransactionData.lease.LeaseFinance.LegalEntity.Id, leaseTransactionData.lease.LeaseFinanceDetail.InsuranceChargeBookingGLTemplateId.Value, true);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermPremiumChargeValue) * -1),
GLEntryItemValues.InsurancePremiumRefundReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermFinanceFeeValue) * -1),
GLEntryItemValues.UnearnedInsuranceFinanceCharge,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermServiceFeeValue) * -1),
GLEntryItemValues.UnearnedInsuranceServiceFee,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
false,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermPremiumChargeValue) * -1),
GLEntryItemValues.LongTermInsurancePremiumReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);

GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermFinanceFeeValue) * -1),
GLEntryItemValues.LongTermInsuranceFinanceChargeReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.AddLineItem(leaseTransactionData.lease.Contract.Id,
GLEntityTypeValues.Contract,
new Money(leaseTransactionData.lease.LeaseFinance.CurrencyCode, Convert.ToDecimal(leaseTransactionData.transaction.FullTermServiceFeeValue) * -1),
GLEntryItemValues.LongTermInsuranceServiceFeeReceivable,
$"Sequence #: {leaseTransactionData.lease.Contract.SequenceNumber} , Insurance Charge Cancellation",
leaseTransactionData.lease.LeaseFinance.InstrumentTypeId.Value,
leaseTransactionData.lease.LeaseFinance.LineofBusinessId.Value,
true,
sourceId: leaseTransactionData.transaction.InsuranceChargeId,
lineItemEntity: leaseTransactionData.lease.Contract,
matchingGlTemplate: null,
costCenterId: leaseTransactionData.lease.Contract.CostCenterId);
GLJournalHelper.MakeEntries();
}
}
}

private void FillTransactionLeaseData()
{
leaseNumbers = transactionsToProcessJournals.Select(x => x.LeaseNumber).Distinct().ToList();
leaseDetailsOfTransactions = (from contract in repository.Contracts
join leaseFinance in repository.LeaseFinances
on contract.Id equals leaseFinance.ContractId
join leaseFinanceDetail in repository.LeaseFinanceDetails
on leaseFinance.Id equals leaseFinanceDetail.Id
join legalEntity in repository.LegalEntities
on leaseFinance.LegalEntityId equals legalEntity.Id
join businessUnit in repository.BusinessUnits
on legalEntity.BusinessUnitId equals businessUnit.Id
join glFinancialOpenPeriod in repository.GLFinancialOpenPeriods
on legalEntity.Id equals glFinancialOpenPeriod.LegalEntityId
join leaseNumber in repository.ConvertToStringTable(leaseNumbers, "leaseNumber")
on contract.SequenceNumber equals leaseNumber.Value
where leaseFinance.IsCurrent
&&
(
leaseFinance.BookingStatus.Value == LeaseBookingStatusValues.Commenced.Value
||
leaseFinance.BookingStatus.Value == LeaseBookingStatusValues.FullyPaidOff.Value
)
&& glFinancialOpenPeriod.IsCurrent
select new LeaseTransactions
{
Contract = contract,
LeaseFinance = leaseFinance,
LeaseFinanceDetail = leaseFinanceDetail,
FromBusinessDate = glFinancialOpenPeriod.FromDate,
ToBusinessDate = glFinancialOpenPeriod.ToDate,
CurrentBusinessDate = businessUnit.CurrentBusinessDate
}).ToList();

leasePaymentScheduleOfTransactions = (from contract in repository.Contracts
join leaseFinance in repository.LeaseFinances
on contract.Id equals leaseFinance.ContractId
join leaseFinanceDetail in repository.LeaseFinanceDetails
on leaseFinance.Id equals leaseFinanceDetail.Id
join paymentSchedule in repository.LeasePaymentSchedules
on leaseFinanceDetail.Id equals paymentSchedule.LeaseFinanceDetailId
join leaseNumber in repository.ConvertToStringTable(leaseNumbers, "leaseNumber")
on contract.SequenceNumber equals leaseNumber.Value
where leaseFinance.IsCurrent
&& paymentSchedule.IsActive
&& paymentSchedule.PaymentType.Value == LeasePaymentTypeValues.FixedTerm.Value
select paymentSchedule).ToList();

cdtEventsOfLease = (from contract in repository.Contracts
join cdtEvent in repository.CDTEvents
on contract.Id equals cdtEvent.ContractId
join leaseNumber in repository.ConvertToStringTable(leaseNumbers, "leaseNumber")
on contract.SequenceNumber equals leaseNumber.Value
where cdtEvent.IsActive
select cdtEvent).ToList();
reasonCodes = (from reasonCode in repository.InsuranceVendorReasonCodeConfigs
where reasonCode.IsActive
select reasonCode).ToList();
}

private long? CheckForSundryReceivableCodeInGlobalParam(string globalParamValue)
{
string sundryReceivableCodeForInsuranceCancellation = GlobalParamProvider.GetValue(globalParamValue, string.Empty);
return (from receivableCode in repository.ReceivableCodes
where receivableCode.Name == sundryReceivableCodeForInsuranceCancellation
&& receivableCode.IsActive
&& (receivableCode.ReceivableType.Name.Value == ReceivableTypeValues.Sundry.Value || receivableCode.ReceivableType.Name.Value == ReceivableTypeValues.SundrySeparate.Value)
select receivableCode.Id).First();
}

private void ProcessSingleTransactions(int iteratorCount)
{
try
{
Logger.LogInfo(@"Setting of Prior Cancellation Flag");
SetIsPriorCancellations(iteratorCount);

Logger.LogInfo(@"Validation of Single Transactions");
ValidateSingleTransactions(iteratorCount);

Logger.LogInfo(@"Creation of Charges of Single Transactions");
var validSingleChargeCreationTransactions = CreateChargesForSingleTransactions(iteratorCount);

Logger.LogInfo(@"Processing of O and X transactions");
ProcessOandXTransaction(iteratorCount);

Logger.LogInfo(@"Cancellation of Charges of Single Transactions");
var validSingleChargeCancellationTransactions = ProcessCTransaction(iteratorCount);

transactionsToProcessJournals = transactionsToProcessJournals.Union(validSingleChargeCreationTransactions).Union(validSingleChargeCancellationTransactions).ToList();
}
catch (Exception ex)
{
if (((SqlException)ex.InnerException).Number == 51000)
{
Logger.LogException(ex,"Skipping process of bulk Creation, Cancellation, O/X transactions of File : " + fileName);
throw;
}
else
{
Logger.LogException(ex,"Error Message : " + ex.Message + "rn" + "Inner Exception : " + ex.InnerException + "rn" + "Target Site : " + ex.TargetSite + "rn" + "Stack Trace : " + ex.StackTrace + "rn" + "Source : " + ex.Source);
throw;
}
}
}

private List<BDTTransaction> ProcessCTransaction(int iteratorCount)
{
var chargeCreationStoredProc = new StoredProc("ProcessCTransactions", typeof(Lw.Domain.Project.Extension.Tasks.ReceivableIds), typeof(Lw.Domain.Project.Extension.Tasks.ReceivableIds), typeof(BDTTransaction), typeof(BDTReceivableInfo));
SqlParameter[] param = GetSQLParametersForInsuranceChargeCancellation(iteratorCount);
var result = repository.ExecuteStoredProc(chargeCreationStoredProc, param);
if (result[0].Any())
{
reverseReceivables.AddRange(result[0].Cast<Lw.Domain.Project.Extension.Tasks.ReceivableIds>().Select(x => x.ReceivableId).ToList());
}
if (result[1].Any())
{
negativeReceivables.AddRange(result[1].Cast<Lw.Domain.Project.Extension.Tasks.ReceivableIds>().ToList());
}
if (result[3].Any())
{
_contractReceivableInfo.AddRange(result[3].Cast<BDTReceivableInfo>().Select(x => new BDTReceivableInfo { EntityId = x.EntityId, EntityType = x.EntityType, ReceivableTypeId = x.ReceivableTypeId, ReceivableTypeName = x.ReceivableTypeName }));
}

return result[2].Cast<BDTTransaction>().ToList();
}

private SqlParameter[] GetSQLParametersForInsuranceChargeCancellation(int iteratorCount)
{
return new[]
{
new SqlParameter("@FilePath", SqlDbType.Text)
{
Value = filePath
},
new SqlParameter("@JobStepInstanceId", SqlDbType.BigInt)
{
Value = jobStepInstanceId
},
new SqlParameter("@CreatedById", SqlDbType.BigInt)
{
Value = createdById
},
new SqlParameter("@InsurancePremiumChargeReceivableType",SqlDbType.NVarChar)
{
Value=ReceivableTypeValues.InsurancePremiumCharge.Value
},
new SqlParameter("@InsuranceFinanceFeeReceivableType",SqlDbType.NVarChar)
{
Value=ReceivableTypeValues.InsuranceFinanceFee.Value
},
new SqlParameter("@InsuranceServiceFeeReceivableType",SqlDbType.NVarChar)
{
Value=ReceivableTypeValues.InsuranceServiceFee.Value
},
new SqlParameter("@CurrentBusinessDate", SqlDbType.DateTime)
{
Value = CurrentBusinessDate
},
new SqlParameter("@IteratorCount", SqlDbType.Int)
{
Value = iteratorCount
}
};
}

private void ProcessOandXTransaction(int iteratorCount)
{
var chargeCreationStoredProc = new StoredProc("ProcessOandXTransactions", typeof(BDTTransaction));
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
repository.ExecuteStoredProc(chargeCreationStoredProc, param);
}

private List<BDTTransaction> CreateChargesForSingleTransactions(int iteratorCount)
{
var chargeCreationStoredProc = new StoredProc("CreateBDTInsuranceCharges", typeof(BDTTransaction), typeof(BDTReceivableInfo));
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
var result = repository.ExecuteStoredProc(chargeCreationStoredProc, param);
if (result[1].Any())
{
_contractReceivableInfo = result[1].Cast<BDTReceivableInfo>().ToList();
}
return result[0].Cast<BDTTransaction>().ToList();
}

private void ValidateSingleTransactions(int iteratorCount)
{
ValidateXSDAssertsForSingleTransactions(iteratorCount);
ValidateMandatoryFieldsForSingleTransactions(iteratorCount);
ValidateBusinessScenariosForSingleTransactions(iteratorCount);
}

private void ValidateBusinessScenariosForSingleTransactions(int iteratorCount)
{
var businessValidationStoredProc = new StoredProc("ValidateBDTBusinessScenarios");
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
repository.ExecuteStoredProc(businessValidationStoredProc, param);
}

private void ValidateMandatoryFieldsForSingleTransactions(int iteratorCount)
{
var mandatoryValidationStoredProc = new StoredProc("ValidateBDTMandatoryFields");
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
repository.ExecuteStoredProc(mandatoryValidationStoredProc, param);
}

private void ValidateXSDAssertsForSingleTransactions(int iteratorCount)
{
var xsdValidationStoredProc = new StoredProc("ValidateBDTXSDAsserts");
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
repository.ExecuteStoredProc(xsdValidationStoredProc, param);
}

private void SetIsPriorCancellations(int iteratorCount)
{
var receivableUpdateStoredProc = new StoredProc("SetIsPriorCancellations");
SqlParameter[] param = GetSQLParametersForBulkProcessing(iteratorCount);
repository.ExecuteStoredProc(receivableUpdateStoredProc, param);
}

private SqlParameter[] GetSQLParametersForBulkProcessing(int iteratorCount)
{
return new[]
{
new SqlParameter("@FilePath", SqlDbType.Text)
{
Value = filePath
},
new SqlParameter("@JobStepInstanceId", SqlDbType.BigInt)
{
Value = jobStepInstanceId
},
new SqlParameter("@CreatedById", SqlDbType.BigInt)
{
Value = createdById
},
new SqlParameter("@IteratorCount", SqlDbType.Int)
{
Value = iteratorCount
}
};
}
private SqlParameter[] GetSQLParametersForGlobalBulkProcessing()
{
return new[]
{
new SqlParameter("@FilePath", SqlDbType.Text)
{
Value = filePath
},
new SqlParameter("@JobStepInstanceId", SqlDbType.BigInt)
{
Value = jobStepInstanceId
},
new SqlParameter("@CreatedById", SqlDbType.BigInt)
{
Value = createdById
}
};
}
}

}