Notes

CREATE
OR
ALTER PROC [dbo].[BlendedItemReport_C]
@FromDate DATE,
@ToDate DATE,
@Type NVARCHAR(MAX),
@CurrentBusinessUnitId BIGINT,
@CommaSeparatedLegalEntityIds NVARCHAR(MAX),
@CommaSeparatedPrimarySetofBookIds NVARCHAR(MAX),
@CommaSeparatedStatutorySetofBookIds NVARCHAR(MAX)
AS
BEGIN

DECLARE @FiscalMonthStartDate DATETIME;
DECLARE @FiscalMonthEndDate DATETIME;
DECLARE @FiscalYearStartDate DATETIME;
DECLARE @FiscalYearEndDate DATETIME;
DECLARE @FiscalCalendarEndDate DATETIME;
DECLARE @FiscalYear DECIMAL;
DECLARE @CurrentFiscalMonthStartDate DATETIME;
DECLARE @CurrentFiscalMonthEndDate DATETIME;
DECLARE @PreviousFiscalYearStartDate DATETIME;
DECLARE @PreviousFiscalYearEndDate DATETIME
DECLARE @CurrentFiscalYearEndDate DATETIME;
DECLARE @FiscalCalendarYearStartDate DATETIME;
DECLARE @FiscalCalendarYearEndDate DATETIME;
DECLARE @CurrentFiscalYear DECIMAL;

DECLARE @CurrentBusinessDate DATETIME;

DECLARE @Calendar_PreviousMonthEndDate DATETIME;
DECLARE @Calendar_PreviousMonthStartDate DATETIME;
DECLARE @Calendar_PreviousMonthYear DECIMAL;
DECLARE @Calendar_PreviousMonthYearEndDate DATETIME;
DECLARE @Calendar_PreviousMonthYearStartDate DATETIME;
DECLARE @Calendar_PreviousMonthCurrentYearEndDate DATETIME;
DECLARE @Calendar_CurrentMonthStartDate DATETIME;
DECLARE @Calendar_CurrentMonthEndDate DATETIME;
DECLARE @Calendar_EndDate DATETIME;


DECLARE @Subsidy BIT;
DECLARE @BIType NVARCHAR(MAX);
DECLARE @ZERO DECIMAL(16,2);

CREATE TABLE #LegalEntityIds (LegalEntityId Bigint);
DECLARE @InsertStatement NVARCHAR(max) = 'INSERT INTO #LegalEntityIds(LegalEntityId) VALUES ('''+REPLACE(@CommaSeparatedLegalEntityIds,',','''),(''')+''');';
EXEC (@InsertStatement);

SET @ZERO = 0.00
SET @Subsidy = 0;

IF (@Type = 'SubsidyIncome')
BEGIN
SET @Subsidy = 1
SET @BIType = 'Income'
END
ELSE IF (@Type = 'NonSubsidyIncome')
BEGIN
SET @BIType = 'Income'
END
ELSE IF (@Type = 'Expense')
BEGIN
SET @BIType = 'Expense'
END

SELECT TOP 1
@FiscalMonthStartDate = FiscalStartDate,
@FiscalMonthEndDate = FiscalEndDate,
@FiscalYear = FiscalYear
FROM FiscalCalendars
WHERE (SELECT TOP 1 DATEADD(day, -1, FiscalStartDate)
FROM FiscalCalendars
JOIN BusinessUnits ON BusinessUnits.Id = @CurrentBusinessUnitId
WHERE CurrentBusinessDate BETWEEN FiscalStartDate AND FiscalEndDate)
BETWEEN FiscalStartDate AND FiscalEndDate

SELECT TOP 1
@FiscalCalendarEndDate = CalendarEndDate
FROM FiscalCalendars
WHERE DATEADD(day, -1, @FiscalMonthStartDate)
BETWEEN FiscalStartDate AND FiscalEndDate

SELECT
@FiscalYearStartDate = MIN(FiscalStartDate),
@FiscalYearEndDate = @FiscalMonthEndDate,
@CurrentFiscalYearEndDate = MAX(FiscalEndDate)
FROM FiscalCalendars
JOIN BusinessUnits ON BusinessUnits.Id = @CurrentBusinessUnitId
WHERE FiscalYear = @FiscalYear

SELECT TOP 1
@CurrentFiscalMonthStartDate = FiscalStartDate,
@CurrentFiscalMonthEndDate = FiscalEndDate,
@CurrentFiscalYear = FiscalYear
FROM FiscalCalendars
JOIN BusinessUnits ON BusinessUnits.Id = @CurrentBusinessUnitId
WHERE CurrentBusinessDate BETWEEN FiscalStartDate AND FiscalEndDate

SELECT
@PreviousFiscalYearStartDate = MIN(FiscalStartDate),
@PreviousFiscalYearEndDate = MAX(FiscalEndDate)
FROM FiscalCalendars
JOIN BusinessUnits ON BusinessUnits.Id = @CurrentBusinessUnitId
WHERE FiscalYear = @FiscalYear - 1

SELECT
@FiscalCalendarYearEndDate = MAX(CalendarEndDate)
FROM FiscalCalendars
WHERE FiscalYear = @FiscalYear

SELECT
@FiscalCalendarYearStartDate = DATEADD(day, 1, MAX(CalendarEndDate))
FROM FiscalCalendars
WHERE FiscalYear = @FiscalYear - 1

SELECT @CurrentBusinessDate = CurrentBusinessDate
FROM BusinessUnits WHERE Id = @CurrentBusinessUnitId

SET @Calendar_PreviousMonthStartDate = DATEADD(DAY,1,EOMONTH(@CurrentBusinessDate,-2))
SET @Calendar_PreviousMonthEndDate = EOMONTH(@CurrentBusinessDate, -1)
SET @Calendar_PreviousMonthYear = YEAR(@Calendar_PreviousMonthStartDate)
SET @Calendar_EndDate = DATEADD(DAY, -1, @Calendar_PreviousMonthStartDate)
SET @Calendar_PreviousMonthYearStartDate = CONVERT(DATETIME, CAST(@Calendar_PreviousMonthYear AS NVARCHAR)+'-01-01', 102)
SET @Calendar_PreviousMonthYearEndDate = @Calendar_PreviousMonthEndDate
SET @Calendar_PreviousMonthCurrentYearEndDate = CONVERT(DATETIME, CAST(@Calendar_PreviousMonthYear AS NVARCHAR)+'-12-31', 102)
SET @Calendar_CurrentMonthStartDate = DATEADD(DAY,1,EOMONTH(@CurrentBusinessDate,-1))
SET @Calendar_CurrentMonthEndDate = EOMONTH(@CurrentBusinessDate, 0)

CREATE TABLE #PrimarySetofBookIds (PrimarySetofBookId Bigint);
DECLARE @InsertPrimarySetofBookStatement NVARCHAR(max) = 'INSERT INTO #PrimarySetofBookIds(PrimarySetofBookId) VALUES ('''+REPLACE(@CommaSeparatedPrimarySetofBookIds,',','''),(''')+''');';
EXEC (@InsertPrimarySetofBookStatement);

CREATE TABLE #StatutorySetofBookIds (StatutorySetofBookId Bigint);
DECLARE @InsertStatutorySetofBookStatement NVARCHAR(max) = 'INSERT INTO #StatutorySetofBookIds(StatutorySetofBookId) VALUES ('''+REPLACE(@CommaSeparatedStatutorySetofBookIds,',','''),(''')+''');';
EXEC (@InsertStatutorySetofBookStatement);

SELECT LE.Id LegalEntityId,
CASE WHEN PrimarySetOfBooks.PrimarySetofBookId IS NOT NULL THEN 1 ELSE 0 END IsPrimary,
CASE WHEN SecondarySetOfBooks.StatutorySetofBookId IS NOT NULL THEN 1 ELSE 0 END IsSecondary,
LERD.IncorporationStateId,
SecondarySetOfBookForCalender.IncomeScheduleCalendar CalendarType
INTO #AccessibleLegalEntitySetofBookFilter
FROM #LegalEntityIds AccessibleLegalEntities
JOIN LegalEntities LE ON AccessibleLegalEntities.LegalEntityId = LE.Id
JOIN LegalEntityRegionalDetails LERD ON LERD.LegalEntityId=LE.Id
LEFT JOIN #PrimarySetofBookIds PrimarySetOfBooks ON LE.DFSPrimarySetofBookId = PrimarySetOfBooks.PrimarySetofBookId
LEFT JOIN #StatutorySetofBookIds SecondarySetOfBooks ON LE.DFSStatutorySetofBookId = SecondarySetOfBooks.StatutorySetofBookId
LEFT JOIN DFSSetofBooksConfigs SecondarySetOfBookForCalender ON SecondarySetOfBookForCalender.Id = LE.DFSStatutorySetofBookId AND SecondarySetOfBookForCalender.IsPrimary = 0

WHERE PrimarySetOfBooks.PrimarySetofBookId IS NOT NULL OR SecondarySetOfBooks.StatutorySetofBookId IS NOT NULL

SELECT
C.Alias,
C.ChargeOffStatus,
C.Id ContractId,
C.IsNonAccrual,
C.NonAccrualDate,
C.SequenceNumber,
C.DealProductTypeId,
C.CurrencyId,
LF.Id LeaseFinanceId,
LF.CustomerId,
LF.IsCurrent,
LF.BookingStatus,
LESetOfBookFilter.IsSecondary,
LESetOfBookFilter.IsPrimary,
LESetOfBookFilter.CalendarType,
LESetOfBookFilter.IncorporationStateId,
P.PostDate PayoffPostDate
INTO #ContractInfo
FROM
Contracts C
JOIN LeaseFinances LF ON LF.ContractId = C.Id
JOIN #AccessibleLegalEntitySetofBookFilter LESetOfBookFilter ON LF.LegalEntityId = LESetOfBookFilter.LegalEntityId
LEFT JOIN LeaseAmendments LA ON LA.CurrentLeaseFinanceId = LF.Id AND LA.AmendmentType='Payoff'
LEFT JOIN Payoffs P ON P.LeaseFinanceId = LA.OriginalLeaseFinanceId AND P.Status ='Activated'
WHERE C.BackgroundProcessingPending = 0 AND
(BookingStatus='Commenced' OR BookingStatus='FullyPaidOff')
GROUP BY C.Alias,
C.ChargeOffStatus,
C.Id,
C.IsNonAccrual,
C.NonAccrualDate,
C.ChargeOffStatus,
C.SequenceNumber,
C.DealProductTypeId,
C.CurrencyId,
LF.Id,
LF.CustomerId,
LF.IsCurrent,
LF.BookingStatus,
LESetOfBookFilter.IsSecondary,
LESetOfBookFilter.IsPrimary,
LESetOfBookFilter.CalendarType,
LESetOfBookFilter.IncorporationStateId,
P.PostDate

SELECT
CI.ContractId,
SUM(R.TotalAmount_Amount) Gross_Contract
INTO #ReceivableInfo
FROM #ContractInfo CI
JOIN Receivables R ON CI.ContractId = R.EntityId
WHERE
CI.IsCurrent = 1
AND R.IsActive = 1
AND R.IncomeType IN ('FixedTerm','InterimRent')
AND R.EntityType='CT'
GROUP BY CI.ContractId


SELECT
LARebook.AmendmentType,
LFD.PostDate LeaseFinanceDetailPostDate,
LBI.BlendedItemId BIIdentifier,
BI.Description BIKEY,
CASE WHEN CI.Alias IS NOT NULL THEN CI.Alias ELSE CI.SequenceNumber END CONTRACT,
CI.Alias Alias,
PY.PartyName CUSTNAME,
CO.LongName REGION,
CI.ChargeOffStatus BRANCH,
R.Gross_Contract GROSSCONTRACT,
@ZERO EQUIPMENTCOST,
LFD.BookedResidual_Amount RESIDUAL,
LFD.LessorYield COMBIRR,
LBI.YieldwithBlendedItem BlendedYield,
(CASE WHEN BI.Captive = 1 THEN 'Yes' ELSE 'No' END) IsCaptive,
(CASE WHEN BI.IsVendorSubsidy = 1 THEN 'Yes' ELSE 'No' END) IsSubsidy,
BI.Name BICODEDESC,
DPT.Name LEASETYPE,
BI.BookRecognitionMode BIINCMTHD,
CASE WHEN BI.BookRecognitionMode = 'RecognizeImmediately' THEN BID.PostDate ELSE BI.PostDate END BIBookingDate,
BI.OriginalSubsidy_Amount BIInitialSubsidy,
BI.StartDate BIBeginDate,
BI.EndDate BIEndDate,
CONVERT(DECIMAL(10,6),ISNULL((dbo.DaysDifferenceBy30360(BI.StartDate,BI.EndDate)/30),0)) BITerm,
BI.Amount_Amount BIAmount,
@ZERO MTDACCR,
@ZERO YTDACCR,
@ZERO CTDACCR,
@ZERO BIAmountUnearned,
@ZERO OLInitialSubsidy,
@ZERO OLAmorCTD,
@ZERO OLAmorYTD,
@ZERO OLMTDSubsidy,
@ZERO OLRemunearnedsub,
@ZERO FLInitialSubsidy,
@ZERO FLAmorCTD,
@ZERO FLAmorYTD,
@ZERO FLMTDSubsidy,
@ZERO FLRemunearnedsub,
CUR.Name CurrencyDesc,
BI.Id BlendedItemId,
0 HasChildBIs,
CI.LeaseFinanceId,
CAST(NULL AS NVARCHAR(40)) OLRecognitionMethod,
CAST(NULL AS NVARCHAR(40)) FLRecognitionMethod,
CAST(NULL AS BIGINT) BILeaseComponentID,
CAST(NULL AS BIGINT) BINonLeaseComponentID,
CI.ContractId,
CI.IsNonAccrual,
CI.NonAccrualDate,
CI.ChargeOffStatus,
BI.RelatedBlendedItemId,
BI.ParentBlendedItemId,
CI.IsCurrent,
BI.IsActive,
BID.Postdate BIDetailPostDate,
BI.BookRecognitionMode,
BI.SystemConfigType,
CI.BookingStatus,
IsVendorSubsidy,
BI.PostDate BIPostDate,
BI.Type,
CI.PayoffPostDate,
CASE WHEN (CI.IsSecondary = 1 AND CI.CalendarType = 'Calendar' AND BI.OriginalBlendedItemId IS NOT NULL) THEN 1 ELSE 0 END IsGregorian,
CASE WHEN (CI.IsSecondary = 1 AND BI.OriginalBlendedItemId IS NOT NULL) THEN 1 ELSE 0 END IsSecondary,
CASE WHEN (CI.IsPrimary = 1 AND BI.OriginalBlendedItemId IS NULL) THEN 1 ELSE 0 END IsPrimary,
ROW_NUMBER() OVER(PARTITION BY BI.Id ORDER BY CI.LeaseFinanceId DESC) LeaseFinanceOrder
INTO #AllBlendedItemTemp
FROM #ContractInfo CI
JOIN #ReceivableInfo R ON R.ContractId = CI.ContractId
JOIN LeaseBlendedItems LBI ON LBI.LeaseFinanceId = CI.LeaseFinanceId
JOIN BlendedItems BI ON BI.Id = LBI.BlendedItemId
JOIN LeaseFinanceDetails LFD ON CI.LeaseFinanceId = LFD.Id
JOIN DealProductTypes DPT ON DPT.Id = CI.DealProductTypeId
JOIN Parties PY ON CI.CustomerId = PY.Id
JOIN States S ON CI.IncorporationStateId = S.Id
JOIN Countries CO ON CO.Id = S.CountryId
JOIN Currencies CUR ON CUR.Id = CI.CurrencyId
LEFT JOIN LeaseAmendments LARebook ON LARebook.OriginalLeaseFinanceId = CI.LeaseFinanceId AND LARebook.AmendmentType='Rebook' AND LARebook.LeaseAmendmentStatus ='Approved'
LEFT JOIN BlendedItemDetails BID ON BID.BlendedItemId = BI.Id AND BID.IsActive = 1
WHERE BI.Type IN ('Income', 'Expense')
AND ((CI.IsSecondary = 1 AND BI.OriginalBlendedItemId IS NOT NULL) OR
(CI.IsPrimary = 1 AND BI.OriginalBlendedItemId IS NULL))

;WITH CTE
AS
(
SELECT
BlendedItemId,
ContractId,
ParentBlendedItemId,
CAST(','+ CAST(BlendedItemId AS VARCHAR(MAX))AS VARCHAR(MAX)) AS [Hierarchy],
#AllBlendedItemTemp.IsActive
FROM #AllBlendedItemTemp
WHERE ParentBlendedItemId IS NULL
UNION ALL
SELECT
BI.BlendedItemId ,
BI.ContractId,
BI.ParentBlendedItemId ,
[Hierarchy]+','+ CAST(BI.BlendedItemId AS VARCHAR(MAX)) AS [Hierarchy],
CTE.IsActive 'IsActive'
FROM CTE
INNER JOIN #AllBlendedItemTemp BI
ON BI.ParentBlendedItemId = CTE.BlendedItemId
)
SELECT
#AllBlendedItemTemp.BlendedItemId,
#AllBlendedItemTemp.ContractId,
#AllBlendedItemTemp.IsCurrent,
RIGHT([Hierarchy],LEN([Hierarchy])-1) AS ParentChildHierarchyId
INTO #CSVBlendedItemHierarchy
FROM CTE
INNER JOIN #AllBlendedItemTemp ON CTE.BlendedItemId = #AllBlendedItemTemp.BlendedItemId AND #AllBlendedItemTemp.IsActive=1

SELECT BlendedItemId,
value ParentChildHierarchyId,
ContractId,
IsCurrent
INTO #BlendedItemHierarchy
FROM #CSVBlendedItemHierarchy
CROSS APPLY STRING_SPLIT(ParentChildHierarchyId, ',') ParentChildHierarchyId;

SELECT
BlendedItemId,
CAST(ParentChildHierarchyId AS BIGINT) ParentChildHierarchyId,
ContractId,
MAX(CAST(IsCurrent AS BIGINT)) IsCurrent
INTO #AllReversalBlendedItemsWithNoDuplicate
FROM #BlendedItemHierarchy
GROUP BY BlendedItemId, ParentChildHierarchyId, ContractId

SELECT
ROW_NUMBER() OVER(PARTITION BY RBI.ParentChildHierarchyId ORDER BY RBI.IsCurrent DESC, RBI.BlendedItemId DESC) BIOrder,
ROW_NUMBER() OVER(PARTITION BY RBI.BlendedItemId ORDER BY RBI.IsCurrent DESC, RBI.ParentChildHierarchyId DESC) ParentBIOrder,
RBI.BlendedItemId,
RBI.ParentChildHierarchyId,
RBI.ContractId,
RBI.IsCurrent,
BI.SystemConfigType,
BI.RelatedBlendedItemId
INTO #ReversalBlendedItemsWithNoDuplicate
FROM #AllReversalBlendedItemsWithNoDuplicate RBI
JOIN #AllBlendedItemTemp BI ON RBI.ParentChildHierarchyId = BI.BlendedItemId AND BI.IsActive = 1

UPDATE RBI
SET RBI.BlendedItemId = CurrentRBI.BlendedItemId
FROM #ReversalBlendedItemsWithNoDuplicate RBI
JOIN #ReversalBlendedItemsWithNoDuplicate CurrentRBI ON RBI.ContractId = CurrentRBI.ContractId AND RBI.RelatedBlendedItemId = CurrentRBI.RelatedBlendedItemId AND RBI.SystemConfigType = CurrentRBI.SystemConfigType AND CurrentRBI.BIOrder = 1 and CurrentRBI.IsCurrent = 1 AND CurrentRBI.ParentBIOrder = 1
WHERE RBI.BIOrder = 1
AND RBI.IsCurrent = 0
AND RBI.SystemConfigType IN ('ReclassifiedFinanceComponent', 'ReclassifiedLeaseComponent')

SELECT
RBI.BlendedItemId,
RBI.ParentChildHierarchyId ParentId,
MAX(ParentABI.IsPrimary) ParentBIIsPrimary,
MAX(ParentABI.IsSecondary) ParentBIIsSecondary,
MAX(ParentABI.IsGregorian) ParentBIIsGregorian,
MAX(ParentABI.BIDetailPostDate) ParentBIDetailPostDate,
MAX(ParentABI.BIAmount) ParentBIAmount,
ParentABI.SystemConfigType ParentSystemConfigType,
ParentABI.ParentBlendedItemId ParentsParentId,
MAX(ParentsParentABI.IsPrimary) ParentsParentIsPrimary,
MAX(ParentsParentABI.IsSecondary) ParentsParentBIIsSecondary,
MAX(ParentsParentABI.IsGregorian) ParentsParentBIIsGregorian,
MAX(ParentsParentABI.BIDetailPostDate) ParentsParentBIDetailPostDate,
MAX(ParentsParentABI.BIAmount) ParentsParentBIAmount
INTO #ParentsParentBI
FROM #ReversalBlendedItemsWithNoDuplicate RBI
LEFT JOIN #AllBlendedItemTemp ParentABI ON RBI.ParentChildHierarchyId = ParentABI.BlendedItemId AND ParentABI.IsActive = 1
LEFT JOIN #AllBlendedItemTemp ParentsParentABI ON ParentABI.ParentBlendedItemId = ParentsParentABI.BlendedItemId AND ParentsParentABI.IsActive = 1
WHERE ParentABI.BookRecognitionMode = 'RecognizeImmediately' AND RBI.BIOrder = 1
GROUP BY RBI.BlendedItemId, RBI.ParentChildHierarchyId, ParentABI.ParentBlendedItemId, ParentABI.SystemConfigType

SELECT
BlendedItemId,
SUM(CASE WHEN ParentSystemConfigType = 'ReclassifiedFinanceComponent'
AND ((ParentBIIsPrimary = 1 AND ParentBIDetailPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 1 AND ParentBIDetailPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 0 AND ParentBIDetailPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN ISNULL(ParentBIAmount,0.00) - ISNULL(ParentsParentBIAmount,0.00)
ELSE 0 END) FLMTDAmount,

SUM(CASE WHEN ParentSystemConfigType = 'ReclassifiedLeaseComponent'
AND ((ParentBIIsPrimary = 1 AND ParentBIDetailPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 1 AND ParentBIDetailPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 0 AND ParentBIDetailPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN ISNULL(ParentBIAmount,0.00) - ISNULL(ParentsParentBIAmount,0.00)
ELSE 0 END) OLMTDAmount,

SUM(CASE WHEN ParentSystemConfigType = 'ReclassifiedFinanceComponent'
AND ((ParentBIIsPrimary = 1 AND ParentBIDetailPostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 1 AND ParentBIDetailPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 0 AND ParentBIDetailPostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN ISNULL(ParentBIAmount,0.00) - ISNULL(ParentsParentBIAmount,0.00)
ELSE 0 END) FLYTDAmount,

SUM(CASE WHEN ParentSystemConfigType = 'ReclassifiedLeaseComponent'
AND ((ParentBIIsPrimary = 1 AND ParentBIDetailPostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 1 AND ParentBIDetailPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(ParentBIIsSecondary = 1 AND ParentBIIsGregorian = 0 AND ParentBIDetailPostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN ISNULL(ParentBIAmount,0.00) - ISNULL(ParentsParentBIAmount,0.00)
ELSE 0 END) OLYTDAmount,
SUM(CASE WHEN ParentSystemConfigType IN ('ReclassifiedLeaseComponent','ReclassifiedFinanceComponent' ) THEN 1 ELSE 0 END) HasOLorFLComponents
INTO #RecognizeImmediatelyBI
FROM #ParentsParentBI
GROUP BY BlendedItemId

SELECT
*
INTO #BlendedItemTempWithAllLeaseFinance
FROM #AllBlendedItemTemp
WHERE IsActive = 1
AND (BookingStatus='Commenced' OR (BookingStatus='FullyPaidOff' AND ((IsPrimary = 1 AND PayoffPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate OR PayoffPostDate BETWEEN @CurrentFiscalMonthStartDate AND @CurrentFiscalMonthEndDate) OR
(IsSecondary = 1 AND IsGregorian = 1 AND PayoffPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate OR PayoffPostDate BETWEEN @Calendar_CurrentMonthStartDate AND @Calendar_CurrentMonthEndDate) OR
(IsSecondary = 1 AND IsGregorian = 0 AND PayoffPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate OR PayoffPostDate BETWEEN @CurrentFiscalMonthStartDate AND @CurrentFiscalMonthEndDate))))
AND ((@Type = 'ALL' AND IsVendorSubsidy IN (1,0) AND Type IN ('Income','Expense')) OR (@Type <> 'ALL' AND IsVendorSubsidy = @Subsidy AND Type = @BIType))
AND (@FromDate IS NULL OR CAST(CASE WHEN BookRecognitionMode = 'RecognizeImmediately' THEN BIDetailPostDate ELSE BIPostDate END AS DATE) >= CAST(@FromDate AS DATE))
AND (@ToDate IS NULL OR CAST(CASE WHEN BookRecognitionMode = 'RecognizeImmediately' THEN BIDetailPostDate ELSE BIPostDate END AS DATE) <= CAST(@ToDate AS DATE))

SELECT
BlendedItemId,
MAX(IsPrimary) IsPrimary,
MAX(IsSecondary) IsSecondary,
MAX(IsGregorian) IsGregorian,
MAX(NonAccrualDate) NonAccrualDate
INTO #OnlyBlendedItemInfo
FROM #BlendedItemTempWithAllLeaseFinance
GROUP by BlendedItemId

SELECT *
INTO #BlendedItemTemp
FROM #BlendedItemTempWithAllLeaseFinance
WHERE IsCurrent = 1

SELECT
ContractId
INTO #DistinctBIContracts
FROM #BlendedItemTemp
GROUP BY ContractId

SELECT
BlendedItemId,
MAX(ContractId) ContractId,
MAX(CAST(IsNonAccrual AS INT)) IsNonAccrual,
MAX(CASE WHEN ChargeOffStatus IN ('ChargedOff','Recovery') THEN 1 ELSE 0 END) IsChargedOff,
MAX(NonAccrualDate) NonAccrualDate,
MAX(BIAmount) BIAmount,
MAX(LeaseFinanceId) LeaseFinanceId,
MAX(IsPrimary) IsPrimary,
MAX(IsSecondary) IsSecondary,
MAX(IsGregorian) IsGregorian
INTO #DistinctBIs
FROM #BlendedItemTemp
GROUP BY BlendedItemId

SELECT
BIContract.ContractId,
CO.ChargeOffDate,
CO.PostDate,
ChargeOffCalendar.FiscalStartDate PostDateFiscalStartDate,
ChargeOffCalendar.FiscalEndDate PostDateFiscalEndDate,
EOMONTH(CO.PostDate, 0) Calendar_PostDateEndDate,
DATEADD(DAY,1,EOMONTH(CO.PostDate,-1)) Calendar_PostDateStartDate
INTO #ChargeOffTemp
FROM #DistinctBIContracts BIContract
JOIN ChargeOffs CO ON BIContract.ContractId = CO.ContractId
JOIN BusinessUnits BusinessUnit ON BusinessUnit.Id = @CurrentBusinessUnitId
LEFT JOIN FiscalCalendars ChargeOffCalendar ON ChargeOffCalendar.BusinessCalendarId = @CurrentBusinessUnitId AND
CO.PostDate BETWEEN ChargeOffCalendar.FiscalStartDate AND ChargeOffCalendar.FiscalEndDate
AND BusinessUnit.PortfolioId = ChargeOffCalendar.PortfolioId
WHERE CO.IsActive = 1 AND CO.Status = 'Approved' AND CO.ReceiptId IS NULL AND CO.IsRecovery = 0

SELECT
BIContract.ContractId,
COR.ChargeOffReversalDate,
COR.ReversalPostDate
INTO #ChargeoffReversalTemp
FROM #DistinctBIContracts BIContract
JOIN ChargeOffReversals COR ON BIContract.ContractId = COR.ContractId
WHERE COR.IsActive = 1 AND COR.Status = 'Approved'

SELECT
ROW_NUMBER() OVER(PARTITION BY NAC.ContractId ORDER BY NA.UpdatedTime DESC) RowFlag,
NAC.ContractId,
NA.PostDate,
NAC.NonAccrualDate,
NonAccrualCalendar.FiscalYear NonAccrualDateFiscalYear,
NonAccrualPostDateCalendar.FiscalStartDate PostDateFiscalStartDate,
NonAccrualPostDateCalendar.FiscalEndDate PostDateFiscalEndDate
INTO #NonAccrualTemp
FROM #DistinctBIContracts BIContract
JOIN NonAccrualContracts NAC ON BIContract.ContractId = NAC.ContractId
JOIN NonAccruals NA ON NAC.NonAccrualId = NA.Id
LEFT JOIN FiscalCalendars NonAccrualCalendar ON NonAccrualCalendar.BusinessCalendarId = @CurrentBusinessUnitId AND
NAC.NonAccrualDate BETWEEN NonAccrualCalendar.FiscalStartDate AND NonAccrualCalendar.FiscalEndDate
LEFT JOIN FiscalCalendars NonAccrualPostDateCalendar ON NonAccrualPostDateCalendar.BusinessCalendarId = @CurrentBusinessUnitId AND
NA.PostDate BETWEEN NonAccrualPostDateCalendar.FiscalStartDate AND NonAccrualPostDateCalendar.FiscalEndDate
WHERE NAC.IsActive = 1 AND NA.Status = 'Approved'

SELECT
RBI.BlendedItemId,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
AND LF.BookingStatus != 'Inactive'
THEN ISNULL(BIS.Income_Amount,0.00)
ELSE 0 END) MTDReversalIncomeAmount,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN (CASE WHEN BIS.Id IS NOT NULL THEN 1 ELSE 0 END)
ELSE 0 END) MTDReversalIncomeCount,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthCurrentYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate)) AND
((BlendedItemTemp.IsPrimary = 1 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.IncomeDate NOT BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate))
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount ELSE 0 END) YTDReversalIncomeAmount,
SUM(CASE WHEN (BIS.IncomeDate < BlendedItemTemp.NonAccrualDate) AND
((BlendedItemTemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthCurrentYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate)) AND
((BlendedItemTemp.IsPrimary = 1 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.IncomeDate NOT BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate))
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount ELSE 0 END) YTDNonAccrualChargedOffReversalIncomeAmount,
SUM((CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
AdjustmentEntry = 1 AND
ModificationType IN ('Rebook','GLTransfer','Payoff') AND
RBI.BlendedItemId != RBI.ParentChildHierarchyId
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0.00 END)) MTDPostingAmount,
SUM((CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
AdjustmentEntry = 0 AND
ModificationType = 'Rebook' AND
RBI.BlendedItemId != RBI.ParentChildHierarchyId
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0.00 END)) MTDPostingAmountIsNotAdjustment,
SUM((CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
AdjustmentEntry = 1 AND
BIS.IsNonAccrual = 0 AND
ModificationType = 'ReAccrual'
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0.00 END)) MTDPostingAmountReAccrual,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
AND AdjustmentEntry = 1
AND BIS.IsNonAccrual = 0
AND ModificationType IN ('Payoff','Rebook','Restructure','ReAccrual','PayoffReversal','NonAccrual')
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0 END) YTDPostingAmount,

SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
AND AdjustmentEntry = 0
AND BIS.IsNonAccrual = 0
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0 END) YTDOldLeaseFinanceBISAmount,

SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
AND AdjustmentEntry = 1
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0 END) MTDNonAccrualAmount,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate <= @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate <= @FiscalMonthEndDate))
AND ModificationType IN ('Payoff','Rebook','Restructure','ReAccrual','PayoffReversal','NonAccrual')
AND AdjustmentEntry = 1
AND BIS.IsNonAccrual = 0
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount
ELSE 0 END) CTDPostingAmount,
SUM(CASE WHEN ((BlendedItemTemp.IsPrimary = 1 AND BIS.PostDate <= @FiscalMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 1 AND BIS.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BlendedItemTemp.IsSecondary = 1 AND BlendedItemTemp.IsGregorian = 0 AND BIS.PostDate <= @FiscalMonthEndDate))
AND BIS.AdjustmentEntry = 0
AND BIS.IsNonAccrual = 0
AND BIS.LeaseFinanceId != ISNULL(BlendedItemTemp_CurrentLeaseFinance.LeaseFinanceId,0)
THEN BIS.Income_Amount ELSE 0 END) CTDOldLeaseFinanceBISAmount
INTO #ReversalBlendedItems
FROM #ReversalBlendedItemsWithNoDuplicate RBI
JOIN BlendedIncomeSchedules BIS ON RBI.ParentChildHierarchyId = BIS.BlendedItemId
JOIN LeaseFinances LF ON LF.Id = BIS.LeaseFinanceId
LEFT JOIN #OnlyBlendedItemInfo BlendedItemTemp ON BlendedItemTemp.BlendedItemId = RBI.ParentChildHierarchyId
LEFT JOIN #BlendedItemTemp BlendedItemTemp_CurrentLeaseFinance ON BlendedItemTemp_CurrentLeaseFinance.BlendedItemId = RBI.ParentChildHierarchyId
WHERE RBI.BIOrder = 1
GROUP BY RBI.BlendedItemId

SELECT
ROW_NUMBER() OVER(PARTITION BY BIS.BlendedItemId, BIS.IncomeDate ORDER BY BIS.Id DESC) RowFlag,
BIS.Id BlendedIncomeScheduleId,
BIS.BlendedItemId,
BIS.Income_Amount
INTO #DistinctBISForChargeOffAndNonAccrual
FROM #DistinctBIs BI
JOIN BlendedIncomeSchedules BIS ON BI.BlendedItemId = BIS.BlendedItemId AND BIS.AdjustmentEntry = 0
WHERE BI.IsNonAccrual = 1 and BI.IsChargedOff = 1

SELECT
ROW_NUMBER() OVER(PARTITION BY BIS.BlendedItemId ORDER BY BIS.IncomeDate ASC) RowFlag,
BIS.BlendedItemId,
BIS.Income_Amount,
BIS.IncomeBalance_Amount
INTO #DistinctChargeoffReversalBlendedIncomeScheduleForAccrual
FROM #DistinctBIs BITemp
JOIN BlendedIncomeSchedules BIS ON BITemp.BlendedItemId = BIS.BlendedItemId
JOIN #ChargeoffReversalTemp ChargeOffReversal ON ChargeOffReversal.ContractId = BITemp.ContractId
WHERE BIS.ModificationType = 'ChargeOffReversal'
AND BIS.IncomeDate > ChargeOffReversal.ChargeOffReversalDate
AND ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND ((BITemp.IsPrimary = 1 AND ChargeOffReversal.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND ChargeOffReversal.ReversalPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND ChargeOffReversal.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))

SELECT
BIS.BlendedItemId,
CASE WHEN BITemp.IsNonAccrual = 0 OR (BITemp.IsNonAccrual = 1 AND NonAccrual.NonAccrualDate >= CASE WHEN IsPrimary = 1 THEN @FiscalMonthEndDate ELSE CASE WHEN IsGregorian = 1 THEN @Calendar_PreviousMonthEndDate ELSE @FiscalMonthEndDate END END)
THEN SUM((CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.IsNonAccrual = 0
THEN BIS.Income_Amount
ELSE 0.00 END) -
(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND BIS.LeaseFinanceId = BITemp.LeaseFinanceId
THEN BIS.Income_Amount
ELSE 0.00 END))
-SUM((CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.AdjustmentEntry = 1 AND
BIS.LeaseFinanceId = BITemp.LeaseFinanceId AND
BIS.ModificationType = 'ChargeOffReversal'
THEN BIS.Income_Amount
ELSE 0.00 END))
-MAX(ISNULL(RBI.MTDReversalIncomeAmount,0.00))
+MAX(ISNULL(RBI.MTDPostingAmount,0.00))
+MAX(ISNULL(RBI.MTDPostingAmountIsNotAdjustment,0.00))
+MAX(ISNULL(RBI.MTDPostingAmountReAccrual,0.00))
-(MAX(ISNULL(DistinctChargeoffReversalBISAccrual.Income_Amount,0.00)) + MAX(ISNULL(DistinctChargeoffReversalBISAccrual.IncomeBalance_Amount,0.00)))

WHEN BITemp.IsChargedOff = 1 AND
((BITemp.IsPrimary = 1 AND ChargeOff.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND ChargeOff.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND ChargeOff.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN SUM(-(CASE WHEN BIS.IsNonAccrual = 0 AND
((BITemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN ChargeOff.PostDateFiscalStartDate AND ChargeOff.PostDateFiscalEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN ChargeOff.Calendar_PostDateStartDate AND ChargeOff.Calendar_PostDateEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN ChargeOff.PostDateFiscalStartDate AND ChargeOff.PostDateFiscalEndDate))
THEN BIS.Income_Amount
ELSE 0.00 END)
+(CASE WHEN BIS.IncomeDate >= NonAccrual.NonAccrualDate AND DistinctBISChargeOffAndNonAccrual.BlendedIncomeScheduleId IS NOT NULL
THEN BIS.Income_Amount
ELSE 0.00 END)
+(CASE WHEN BIS.IsNonAccrual = 0 AND
((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN BIS.Income_Amount
ELSE 0.00 END))
+MAX(ISNULL(RBI.MTDPostingAmount,0.00))

WHEN BITemp.IsChargedOff = 0
AND ((BITemp.IsPrimary = 1 AND NonAccrual.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND NonAccrual.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND NonAccrual.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND RBI.MTDReversalIncomeCount = 0
THEN SUM((CASE WHEN BIS.IsNonAccrual = 0 AND
((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.AdjustmentEntry = 1 AND
BIS.ModificationType = 'NonAccrual'
THEN BIS.Income_Amount ELSE 0.00 END)
-(CASE WHEN BIS.IsNonAccrual = 1 AND
((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
((BITemp.IsPrimary = 1 AND BIS.IncomeDate <= @FiscalCalendarEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.IncomeDate <= @Calendar_EndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.IncomeDate <= @FiscalCalendarEndDate)) AND
BIS.LeaseFinanceId = BITemp.LeaseFinanceId
THEN BIS.Income_Amount ELSE 0 END)
+(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.AdjustmentEntry = 1
THEN BIS.Income_Amount
ELSE 0.00 END)

+(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND BIS.IsNonAccrual = 0
AND BIS.ModificationType != 'NonAccrual'
AND BIS.AdjustmentEntry = 0
THEN BIS.Income_Amount ELSE 0.00 END)
)
+MAX(ISNULL(RBI.MTDNonAccrualAmount,0.00))
WHEN BITemp.IsChargedOff = 0
AND ((BITemp.IsPrimary = 1 AND NonAccrual.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND NonAccrual.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND NonAccrual.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND RBI.MTDReversalIncomeCount > 0
THEN -1 * MAX(ISNULL(RBI.MTDReversalIncomeAmount,0.00))
WHEN BITemp.IsChargedOff = 0
AND ((BITemp.IsPrimary = 1 AND NonAccrual.PostDate NOT BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND NonAccrual.PostDate NOT BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND NonAccrual.PostDate NOT BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN SUM((CASE WHEN BIS.IsNonAccrual = 0 AND
((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.AdjustmentEntry = 1
THEN BIS.Income_Amount
ELSE 0.00 END)) -
(SUM(CASE WHEN BIS.IsNonAccrual = 1 AND ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
AND BIS.AdjustmentEntry = 1
AND BIS.ModificationType = 'ChargeOffReversal'
THEN BIS.Income_Amount
ELSE 0.00 END)) -
SUM((CASE WHEN BIS.IsNonAccrual = 0 AND
((BITemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)) AND
BIS.AdjustmentEntry = 1
THEN BIS.Income_Amount
ELSE 0.00 END))
-(MAX(ISNULL(DistinctChargeoffReversalBISAccrual.Income_Amount,0.00)) + MAX(ISNULL(DistinctChargeoffReversalBISAccrual.IncomeBalance_Amount,0.00)))
ELSE 0.00
END MTDSubsidy,
CASE WHEN BITemp.IsNonAccrual = 0
THEN SUM(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate <= @FiscalMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate <= @FiscalMonthEndDate)) AND
BIS.IsNonAccrual = 0 AND
BIS.LeaseFinanceId = BITemp.LeaseFinanceId THEN BIS.Income_Amount ELSE 0 END)
+MAX(ISNULL(RBI.CTDPostingAmount,0.00))
+MAX(ISNULL(RBI.CTDOldLeaseFinanceBISAmount,0.00))
WHEN BITemp.IsChargedOff = 1
THEN BITemp.BIAmount
WHEN BITemp.IsChargedOff = 0
THEN SUM(CASE WHEN BIS.PostDate IS NOT NULL AND BIS.IsNonAccrual = 0 THEN BIS.Income_Amount ELSE 0 END)
ELSE 0.00 END AmorCTD,
CASE WHEN BITemp.IsNonAccrual = 0 OR (BITemp.IsNonAccrual = 1 AND BITemp.IsChargedOff = 0)
THEN SUM(CASE WHEN BIS.IsNonAccrual = 0
THEN ((CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate)) AND
(BIS.LeaseFinanceId = BITemp.LeaseFinanceId)
THEN BIS.Income_Amount ELSE 0 END) -
(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthCurrentYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate)) AND
((BITemp.IsPrimary = 1 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.IncomeDate NOT BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate)) AND
(BIS.LeaseFinanceId = BITemp.LeaseFinanceId)
THEN BIS.Income_Amount ELSE 0 END))
ELSE 0 END)
-MAX(ISNULL(RBI.YTDReversalIncomeAmount,0.00))
+MAX(ISNULL(RBI.YTDPostingAmount,0.00))
+MAX(ISNULL(RBI.YTDOldLeaseFinanceBISAmount,0.00))
WHEN BITemp.IsChargedOff = 1 AND
((BITemp.IsPrimary = 1 AND NonAccrual.NonAccrualDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND NonAccrual.NonAccrualDate NOT BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND NonAccrual.NonAccrualDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate))
THEN SUM(CASE WHEN BIS.IncomeDate >= ChargeOff.ChargeOffDate
AND BIS.LeaseFinanceId = BITemp.LeaseFinanceId
AND DistinctBISChargeOffAndNonAccrual.BlendedIncomeScheduleId IS NOT NULL
THEN BIS.Income_Amount
ELSE 0.00 END)
WHEN BITemp.IsChargedOff = 1 AND
((BITemp.IsPrimary = 1 AND NonAccrual.NonAccrualDate BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND NonAccrual.NonAccrualDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND NonAccrual.NonAccrualDate BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate))
THEN
(SUM((CASE WHEN BIS.IncomeDate >= NonAccrual.NonAccrualDate AND DistinctBISChargeOffAndNonAccrual.BlendedIncomeScheduleId IS NOT NULL
THEN BIS.Income_Amount
ELSE 0.00 END))) +
(SUM(CASE WHEN BIS.IncomeDate < NonAccrual.NonAccrualDate AND BIS.IsNonAccrual = 0
THEN ((CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN BIS.Income_Amount ELSE 0 END) -
(CASE WHEN ((BITemp.IsPrimary = 1 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.ReversalPostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthCurrentYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.ReversalPostDate BETWEEN @FiscalYearStartDate AND @CurrentFiscalYearEndDate)) AND
((BITemp.IsPrimary = 1 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 1 AND BIS.IncomeDate NOT BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BITemp.IsSecondary = 1 AND BITemp.IsGregorian = 0 AND BIS.IncomeDate NOT BETWEEN @FiscalCalendarYearStartDate AND @FiscalCalendarYearEndDate)) AND
(BIS.LeaseFinanceId = BITemp.LeaseFinanceId)
THEN BIS.Income_Amount ELSE 0 END))
ELSE 0 END)
-MAX(ISNULL(RBI.YTDNonAccrualChargedOffReversalIncomeAmount,0.00)))
ELSE 0.00
END AmorYTD
INTO #BlendedIncomeScheduleCalc
FROM #DistinctBIs BITemp
JOIN BlendedIncomeSchedules BIS ON BITemp.BlendedItemId = BIS.BlendedItemId
LEFT JOIN #DistinctBISForChargeOffAndNonAccrual DistinctBISChargeOffAndNonAccrual ON DistinctBISChargeOffAndNonAccrual.BlendedIncomeScheduleId = BIS.Id AND DistinctBISChargeOffAndNonAccrual.RowFlag = 1
LEFT JOIN #DistinctChargeoffReversalBlendedIncomeScheduleForAccrual DistinctChargeoffReversalBISAccrual ON DistinctChargeoffReversalBISAccrual.BlendedItemId = BITemp.BlendedItemId AND DistinctChargeoffReversalBISAccrual.RowFlag = 1
LEFT JOIN #ReversalBlendedItems RBI ON RBI.BlendedItemId = BIS.BlendedItemId
LEFT JOIN #ChargeOffTemp ChargeOff ON ChargeOff.ContractId = BITemp.ContractId
LEFT JOIN #NonAccrualTemp NonAccrual ON NonAccrual.ContractId = BITemp.ContractId AND BITemp.NonAccrualDate = NonAccrual.NonAccrualDate AND NonAccrual.RowFlag = 1
GROUP BY BIS.BlendedItemId, BITemp.IsChargedOff, BITemp.IsNonAccrual, BITemp.BIAmount, ChargeOff.PostDate, NonAccrual.NonAccrualDate, RBI.BlendedItemId, NonAccrual.PostDate, BITemp.IsPrimary, BITemp.IsSecondary, BITemp.IsGregorian, RBI.MTDReversalIncomeCount


SELECT
*
INTO #BlendedItemReport
FROM #BlendedItemTemp
WHERE RelatedBlendedItemId IS NULL

SELECT
LA.LeaseFinanceId,
SUM(LA.NBV_Amount) NetInvestment_Amount
INTO #LeaseAssetInfo
FROM #ContractInfo BI
JOIN LeaseAssets LA ON BI.LeaseFinanceId = LA.LeaseFinanceId
WHERE BI.IsCurrent = 1
AND LA.IsActive = 1
GROUP BY LA.LeaseFinanceId

UPDATE BI
SET BI.EQUIPMENTCOST = LAI.NetInvestment_Amount
FROM #BlendedItemReport BI
JOIN #LeaseAssetInfo LAI ON LAI.LeaseFinanceId = BI.LeaseFinanceId

--************************BEGIN: Income Info for Child BIs********************************
SELECT
BI.RelatedBlendedItemId,
SUM(CASE WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN BI.Amount_Amount ELSE 0.00 END) FLInitialSubsidy,
SUM(CASE WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN BI.Amount_Amount ELSE 0.00 END) OLInitialSubsidy,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN MTDSubsidy
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN RIBI.FLMTDAmount
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) FLMTDSubsidy,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN MTDSubsidy
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN RIBI.OLMTDAmount
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) OLMTDSubsidy,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN AmorCTD
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND BID.IsGLPosted = 1 AND
((BIR.IsPrimary = 1 AND BID.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND BID.IsGLPosted = 1 AND
((BIR.IsPrimary = 1 AND BI.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) FLAmorCTD,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN AmorCTD
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND BID.IsGLPosted = 1 AND
((BIR.IsPrimary = 1 AND BID.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND BID.IsGLPosted = 1 AND
((BIR.IsPrimary = 1 AND BI.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BIR.IsSecondary = 1 AND BI.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BIR.IsSecondary = 1 AND BI.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) OLAmorCTD,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN AmorYTD
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN RIBI.FLYTDAmount
WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) FLAmorYTD,
SUM(CASE WHEN Income.BlendedItemId IS NOT NULL AND BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN AmorYTD
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN RIBI.OLYTDAmount
WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) OLAmorYTD,
MAX(LBI.YieldwithBlendedItem) BlendedYield,
MAX(CASE WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN BI.BookRecognitionMode ELSE NULL END) AS OLRecognitionMethod,
MAX(CASE WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN BI.BookRecognitionMode ELSE NULL END) AS FLRecognitionMethod,
MAX(CASE WHEN BI.SystemConfigType = 'ReclassifiedLeaseComponent' THEN BI.Id ELSE NULL END) BILeaseComponentID,
MAX(CASE WHEN BI.SystemConfigType = 'ReclassifiedFinanceComponent' THEN BI.Id ELSE NULL END) BINonLeaseComponentID
INTO #ChildBlendedIncomeInfo
FROM #BlendedItemReport BIR
JOIN BlendedItems BI ON BI.RelatedBlendedItemId = BIR.BlendedItemId
JOIN LeaseBlendedItems LBI ON LBI.BlendedItemId = BI.Id AND LBI.LeaseFinanceId = BIR.LeaseFinanceId
LEFT JOIN #BlendedIncomeScheduleCalc Income ON Income.BlendedItemId = BI.Id
LEFT JOIN BlendedItemDetails BID ON BID.BlendedItemId = BI.Id AND BID.IsActive = 1
LEFT JOIN #RecognizeImmediatelyBI RIBI ON RIBI.BlendedItemId = BI.Id
WHERE BI.Type IN ('Income','Expense') AND BI.IsActive = 1 AND BI.RelatedBlendedItemId IS NOT NULL
GROUP BY BI.RelatedBlendedItemId

UPDATE BIR SET BIR.FLInitialSubsidy = CASE WHEN BIR.IsVendorSubsidy = 1 THEN BIR.BIAmount - BII.OLInitialSubsidy ELSE BII.FLInitialSubsidy END,
BIR.OLInitialSubsidy = BII.OLInitialSubsidy,
BIR.FLMTDSubsidy = BII.FLMTDSubsidy,
BIR.OLMTDSubsidy = BII.OLMTDSubsidy,
BIR.FLAmorCTD = BII.FLAmorCTD,
BIR.OLAmorCTD = BII.OLAmorCTD,
BIR.FLAmorYTD = BII.FLAmorYTD,
BIR.OLAmorYTD = BII.OLAmorYTD,
BIR.BlendedYield = BII.BlendedYield,
BIR.HasChildBIs = 1
FROM #BlendedItemReport BIR
JOIN #ChildBlendedIncomeInfo BII ON BIR.BlendedItemId = BII.RelatedBlendedItemId
WHERE BIR.IsSubsidy = 'Yes'

UPDATE BIR SET
BIR.OLRecognitionMethod = BII.OLRecognitionMethod,
BIR.FLRecognitionMethod = BII.FLRecognitionMethod,
BIR.BILeaseComponentID = BII.BILeaseComponentID,
BIR.BINonLeaseComponentID = BII.BINonLeaseComponentID
FROM #BlendedItemReport BIR
JOIN #ChildBlendedIncomeInfo BII ON BIR.BlendedItemId = BII.RelatedBlendedItemId

--************************BEGIN Income Info for Child BIs********************************

--************************BEGIN: YTDACCR, MTDACCR, CTDACCR********************************
UPDATE BIR SET BIR.YTDACCR = BIR.OLAmorYTD + BIR.FLAmorYTD ,
BIR.MTDACCR = BIR.OLMTDSubsidy + BIR.FLMTDSubsidy,
BIR.CTDACCR = BIR.OLAmorCTD + BIR.FLAmorCTD
FROM #BlendedItemReport BIR WHERE BIR.HasChildBIs = 1

SELECT
BI.Id BlendedItemId,
(CASE WHEN Income.BlendedItemId IS NOT NULL THEN MTDSubsidy
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate)))
THEN CASE WHEN BIR.IsSubsidy = 'Yes' AND RIBI.HasOLorFLComponents > 0 THEN RIBI.OLMTDAmount + RIBI.FLMTDAmount ELSE BI.Amount_Amount END
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate ) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthStartDate AND @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalMonthStartDate AND @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) MTDAccr,
(CASE WHEN Income.BlendedItemId IS NOT NULL THEN AmorYTD
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN CASE WHEN BIR.IsSubsidy = 'Yes' AND RIBI.HasOLorFLComponents > 0 THEN RIBI.OLYTDAmount + RIBI.FLYTDAmount ELSE BI.Amount_Amount END
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate BETWEEN @Calendar_PreviousMonthYearStartDate AND @Calendar_PreviousMonthYearEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate BETWEEN @FiscalYearStartDate AND @FiscalYearEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) YTDAccr,
(CASE WHEN Income.BlendedItemId IS NOT NULL THEN AmorCTD
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode = 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BID.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BID.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BID.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
WHEN BID.IsGLPosted = 1 AND BI.BookRecognitionMode <> 'RecognizeImmediately' AND
((BIR.IsPrimary = 1 AND BI.PostDate <= @FiscalMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 1 AND BI.PostDate <= @Calendar_PreviousMonthEndDate) OR
(BIR.IsSecondary = 1 AND BIR.IsGregorian = 0 AND BI.PostDate <= @FiscalMonthEndDate))
THEN BI.Amount_Amount
ELSE 0.00 END) CTDAccr
INTO #ParentBlendedIncomeInfo
FROM #BlendedItemReport BIR
JOIN BlendedItems BI ON BI.Id = BIR.BlendedItemId
JOIN LeaseBlendedItems LBI ON LBI.BlendedItemId = BI.Id AND LBI.LeaseFinanceId = BIR.LeaseFinanceId
LEFT JOIN #BlendedIncomeScheduleCalc Income ON Income.BlendedItemId = BI.Id
LEFT JOIN BlendedItemDetails BID ON BID.BlendedItemId = BI.Id AND BID.IsActive = 1
LEFT JOIN #RecognizeImmediatelyBI RIBI ON RIBI.BlendedItemId = BI.Id
WHERE BIR.HasChildBIs = 0

UPDATE BIR SET BIR.YTDACCR = BII.YTDACCR,
BIR.MTDACCR = BII.MTDACCR,
BIR.CTDACCR = BII.CTDACCR
FROM #BlendedItemReport BIR
JOIN #ParentBlendedIncomeInfo BII ON BIR.BlendedItemId = BII.BlendedItemId
WHERE BIR.HasChildBIs = 0

--************************END: YTDACCR, MTDACCR, CTDACCR***************************

SELECT
ROW_NUMBER() OVER(PARTITION BY RBI.BlendedItemId ORDER BY RBI.ParentChildHierarchyId, ABI.LeaseFinanceId) OldestLFRow,
RBI.BlendedItemId,
ABI.LeaseFinanceDetailPostDate BIBookingDate,
ABI.BIPostDate BIPostDate
INTO #BIBookingDate
FROM #ReversalBlendedItemsWithNoDuplicate RBI
JOIN #AllBlendedItemTemp ABI ON RBI.ParentChildHierarchyId = ABI.BlendedItemId AND ABI.IsActive = 1
WHERE RBI.BIOrder = 1

SELECT
BI.ContractId
INTO #GLTransferedContracts
FROM #AllBlendedItemTemp BI
JOIN LeaseAmendments GLTransferAmendment ON GLTransferAmendment.CurrentLeaseFinanceId = BI.LeaseFinanceId
WHERE BI.IsActive = 1 AND GLTransferAmendment.AmendmentType = 'GLTransfer' AND GLTransferAmendment.LeaseAmendmentStatus ='Approved'
GROUP BY ContractId

UPDATE BIR SET BIR.BIBookingDate = CASE WHEN #GLTransferedContracts.ContractId IS NOT NULL THEN BIBD.BIPostDate ELSE BIBD.BIBookingDate END
FROM #BlendedItemReport BIR
JOIN #BIBookingDate BIBD ON BIBD.BlendedItemId = BIR.BIIdentifier
LEFT JOIN #GLTransferedContracts ON BIR.ContractId = #GLTransferedContracts.ContractId
WHERE BIBD.OldestLFRow = 1

SELECT
BIIdentifier,
BILeaseComponentID,
BINonLeaseComponentID,
BIKEY,
CONTRACT,
Alias,
CUSTNAME,
REGION,
BRANCH,
GROSSCONTRACT,
EQUIPMENTCOST,
RESIDUAL,
CAST(ROUND(COMBIRR * 100, 6) AS DECIMAL(16,6)) COMBIRR,
CAST(ROUND(BlendedYield * 100, 6) AS DECIMAL(16,6)) BlendedYield,
IsCaptive,
IsSubsidy,
BICODEDESC,
LEASETYPE,
BIINCMTHD,
BIBookingDate,
BIInitialSubsidy,
BIBeginDate,
BIEndDate,
BITerm,
BIAmount,
MTDACCR,
YTDACCR,
CTDACCR,
(BIAmount - CTDACCR) BIAmountUnearned,
OLRecognitionMethod,
OLInitialSubsidy,
OLAmorCTD,
OLMTDSubsidy,
(OLInitialSubsidy - OLAmorCTD) OLRemunearnedsub,
FLRecognitionMethod,
FLInitialSubsidy,
FLAmorCTD,
FLMTDSubsidy,
(FLInitialSubsidy - FLAmorCTD) FLRemunearnedsub,
CurrencyDesc
FROM #BlendedItemReport

DROP TABLE IF EXISTS #AccessibleLegalEntitySetofBookFilter
DROP TABLE IF EXISTS #AllBlendedItemTemp
DROP TABLE IF EXISTS #BIBookingDate
DROP TABLE IF EXISTS #BlendedIncomeScheduleCalc
DROP TABLE IF EXISTS #BlendedItemHierarchy
DROP TABLE IF EXISTS #BlendedItemReport
DROP TABLE IF EXISTS #BlendedItemTemp
DROP TABLE IF EXISTS #ChargeOffTemp
DROP TABLE IF EXISTS #ChildBlendedIncomeInfo
DROP TABLE IF EXISTS #CSVBlendedItemHierarchy
DROP TABLE IF EXISTS #DistinctBIContracts
DROP TABLE IF EXISTS #DistinctBIs
DROP TABLE IF EXISTS #DistinctBlendedIncomeScheduleForChargeOff
DROP TABLE IF EXISTS #DistinctBlendedIncomeScheduleForNonAccrual
DROP TABLE IF EXISTS #LegalEntityIds
DROP TABLE IF EXISTS #NonAccrualTemp
DROP TABLE IF EXISTS #ParentBlendedIncomeInfo
DROP TABLE IF EXISTS #ParentsParentBI
DROP TABLE IF EXISTS #PrimarySetofBookIds
DROP TABLE IF EXISTS #RecognizeImmediatelyBI
DROP TABLE IF EXISTS #ReversalBlendedItems
DROP TABLE IF EXISTS #ReversalBlendedItemsWithNoDuplicate
DROP TABLE IF EXISTS #StatutorySetofBookIds
DROP TABLE IF EXISTS #BlendedItemTempWithAllLeaseFinance
DROP TABLE IF EXISTS #OnlyBlendedItemInfo
DROP TABLE IF EXISTS #DistinctChargeoffReversalBlendedIncomeScheduleForAccrual
DROP TABLE IF EXISTS #ChargeoffReversalTemp
DROP TABLE IF EXISTS #GLTransferedContracts
DROP TABLE IF EXISTS #AllReversalBlendedItemsWithNoDuplicate
DROP TABLE IF EXISTS #BusinessUnits
DROP TABLE IF EXISTS #ContractInfo
DROP TABLE IF EXISTS #DistinctBISForChargeOffAndNonAccrual
DROP TABLE IF EXISTS #ReceivableInfo
DROP TABLE IF EXISTS #LeaseAssetInfo
END
GO