Vos

 

/* LLP On the bais of Principal overdue Ignored Interest OverDue*/

SET NOCOUNT ON;

DECLARE 

@BranchCode VARCHAR (3),

@Today DATETIME,

@FromDate DATE,

@ToDate DATE

SELECT @Today = Today FROM ControlTable (NOLOCK);

--SELECT @FromDate = CONVERT(datetime2,'01-01-2019')

--SELECT @ToDate = CONVERT(datetime2,'18-08-2019' )

SELECT @BranchCode = '001'

--SELECT @BranchCode = 'ALL'

SELECT 

'BBB'=M.BranchCode,'AT'=AcType,M.Obligor,M.ClientCode,M.MainCode,C.Name,

'Address'=Address1,Phone,M.Limit ,

'O/S GoodBaln' = SUM(ISNULL(ABS(M.GoodBaln),0)),

'OverduePrin' = ISNULL(SUM(ISNULL(DuePrincipal,0)-ISNULL(PaidPrincipal,0)) ,0), 

'PrinODDate'= 

(

  SELECT  MIN(DueDate)

FROM LoanRepaySched S (NOLOCK)

WHERE S.MainCode  = M.MainCode

and  (ISNULL(DuePrincipal,0)-ISNULL(PaidPrincipal,0))<>0

),

'Accr. Int' = SUM(ISNULL(M.IntDrAmt,0)),

'OverDueInterest' = ISNULL(SUM(ISNULL(DueInterest,0)-ISNULL(PaidInterest,0)),0) , 

'IntODDate' =

CASE WHEN (SELECT  MIN(DueDate) FROM LoanRepaySched S (NOLOCK)

WHERE S.MainCode  = M.MainCode

and  (ISNULL(DueInterest,0)-ISNULL(PaidInterest,0))<>0

AND ISNULL(DueInterest,0)<>0

GROUP BY MainCode

) > @Today THEN NULL ELSE 

(

SELECT  MIN(DueDate) FROM LoanRepaySched S (NOLOCK)

WHERE S.MainCode  = M.MainCode

and  (ISNULL(DueInterest,0)-ISNULL(PaidInterest,0))<>0

AND ISNULL(DueInterest,0)<>0

GROUP BY MainCode

) END

INTO #TEMPOD

FROM Master M (NOLOCK),ClientTable C (NOLOCK),LoanRepaySched L (NOLOCK)

WHERE M.ClientCode=C.ClientCode

AND M.MainCode=L.MainCode

--AND  L.MainCode = L1.MainCode

--AND  L.MainCode = L2.MainCode 

AND M.AcType NOT IN (SELECT distinct AcType FROM CustTypeAcType WHERE CustTypeCode = '5')

AND DueDate< @Today

AND IsBlocked<>'C'

AND M.BranchCode = CASE WHEN UPPER(@BranchCode) = 'ALL' THEN M.BranchCode ELSE @BranchCode END

AND GoodBaln<0

GROUP BY M.BranchCode, AcType,M.MainCode, C.Name,Address1,Phone,M.Limit, L.MainCode, M.Obligor, M.ClientCode

HAVING (SUM(ISNULL(DuePrincipal,0)-ISNULL(PaidPrincipal,0))<>0 OR SUM(ISNULL(DueInterest,0)-ISNULL(PaidInterest,0))<>0)

UNION ALL

SELECT 

M.BranchCode,AcType,M.Obligor,M.ClientCode,M.MainCode,M.Name, 

Address1,Phone,M.Limit,

'O/S GoodBaln' = ABS(M.GoodBaln),

'OverduePrin' = CASE WHEN M.LimitExpiryDate <= @Today THEN ISNULL(ABS(M.GoodBaln),0) ELSE 0 END,

'PrinODDate' = M.LimitExpiryDate,--CASE WHEN M.LimitExpiryDate <= @Today THEN M.LimitExpiryDate ELSE NULL END,

M.IntDrAmt,

'ODInt'= (SELECT SUM(ISNULL(DrInt,0)) FROM PastDuedList P(NOLOCK) WHERE P.ReferenceNo = M.MainCode AND IsIntDue = 'T'),

--O.IntODDate

(SELECT MIN(DueDate) FROM PastDuedList P(NOLOCK)

WHERE P.ReferenceNo = M.MainCode 

AND IsIntDue = 'T'

) as IntODDate

FROM Master M(NOLOCK), ClientTable C(NOLOCK)

--,

-- (

-- SELECT BranchCode, ReferenceNo, 'IntODDate' = MIN(DueDate) 

-- FROM PastDuedList P(NOLOCK)

-- GROUP BY BranchCode, ReferenceNo

-- ) AS O

WHERE M.IsNormalDr = 'T'

--AND M.MainCode = O.ReferenceNo

--AND M.BranchCode = O.BranchCode

AND M.AcType NOT IN (SELECT distinct AcType FROM CustTypeAcType (NOLOCK) WHERE CustTypeCode = '5')

AND M.BranchCode = CASE WHEN UPPER(@BranchCode) = 'ALL' THEN M.BranchCode ELSE @BranchCode END

AND M.ClientCode = C.ClientCode

AND M.MainCode NOT IN (SELECT distinct MainCode FROM LoanMaster WHERE HasRepaySched = 'T')

and M.AcTypeType ='C'

--SELECT * FROM #TEMPOD

---------

SELECT 

M.BranchCode,M.Obligor,M.ClientCode,MainCode,Name,

'DisbursementDate' = 

CASE WHEN M.AcType<'51' THEN CONVERT(VARCHAR(10),AcOpenDate,103) 

             WHEN (SELECT MoveType From Master(NOLOCK) WHERE MainCode = M.MainCode)= '3'

THEN CONVERT(VARCHAR(10),AcOpenDate,103)                                 

ELSE (SELECT CONVERT(VARCHAR(10),LastDisburseDate,103) FROM LoanMaster(NOLOCK) WHERE MainCode = M.MainCode) END,

'TypesOfCredit' = 

CASE WHEN A.AcTypeDesc = 'LAS ACCOUNT'   THEN 'LOAN AGAINST SHARE'

             WHEN A.AcTypeDesc = 'ML OD ACCOUNT' THEN 'MORTGAGE LOAN O/D ACCOUNT'

                                                 ELSE A.AcTypeDesc END,

M.AcType,

M.Limit AS ApprovedLimit,

ROUND(-Balance,2)  as [O/SPrincipal],

ROUND(IntDrAmt,2)  as [O/SInterest],

'OverDuePrincipal' = 

CASE WHEN (SELECT HasRepaySched FROM LoanMaster(NOLOCK) WHERE MainCode = M.MainCode)= 'T'

THEN (SELECT ISNULL(SUM(ISNULL(DuePrincipal,0)) - SUM(ISNULL(PaidPrincipal,0)),0)

                    FROM LoanRepaySched L (NOLOCK)

                    WHERE L.MainCode = M.MainCode

                    AND   ISNULL(DuePrincipal,0) > ISNULL(PaidPrincipal,0)

                    AND   DATEDIFF(DAY,DueDate,(SELECT Today FROM ControlTable (NOLOCK)))>0

                )

WHEN M.LimitExpiryDate >= (SELECT Today FROM ControlTable (NOLOCK)) Then 0 ELSE -M.Balance  END,

--'Limit Expiry Date' = CONVERT (VARCHAR(12),M.LimitExpiryDate,106),

'Limit Expiry Date' = M.LimitExpiryDate,

'Loan Class'= dbo.f_GetLoanClass(BranchCode,MainCode),

'Prov' = 

CASE 

WHEN dbo.f_GetLoanClass(BranchCode,MainCode) IN ('Good','UnDue') THEN 1

WHEN dbo.f_GetLoanClass(BranchCode,MainCode) = 'WatchList' THEN 2

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) = 'Substandard' THEN 3

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) = 'Doubtful' THEN 4

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) = 'Bad' THEN 5

         END,

'ProvAmt'= 

CASE 

WHEN dbo.f_GetLoanClass(BranchCode,MainCode) IN ('Good','UnDue') THEN ROUND(-Balance * 0.01,2)

WHEN dbo.f_GetLoanClass(BranchCode,MainCode) ='WatchList' THEN ROUND(-Balance * 0.05,2)

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) ='Substandard' THEN ROUND(-Balance * 0.25,2)

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) ='Doubtful' THEN ROUND(-Balance * 0.5,2)

            WHEN dbo.f_GetLoanClass(BranchCode,MainCode) ='Bad' THEN ROUND(-Balance,2) 

END

INTO #TEMP

FROM Master M (NOLOCK),AcTypeTable A (NOLOCK)

WHERE M.AcType = A.AcType 

AND M.BranchCode = CASE WHEN UPPER(@BranchCode) = 'ALL' THEN M.BranchCode ELSE @BranchCode END

AND A.IsNormalDr = 'T'

AND M.Balance< 0

--AND M.AcType < '51'

AND A.AcTypeType = 'C'

AND A.AcType NOT IN (SELECT DISTINCT AcType FROM CustTypeAcType (NOLOCK) WHERE CustTypeCode = '5')

ORDER BY 1,2,3

--select * from #TEMP

--SELECT PrinODDate,(SELECT Today FROM ControlTable (NOLOCK)),* from #TEMPOD

SELECT 

T.BranchCode,T.Obligor,T.ClientCode,T.MainCode,T.Name,T.DisbursementDate,T.TypesOfCredit,T.AcType,

T.ApprovedLimit, 

T.[O/SPrincipal], 

T.[O/SInterest], 

T.[OverDuePrincipal],

CONVERT(VARCHAR(13),T.[Limit Expiry Date],103) AS [Limit Expiry Date], 

T.[Loan Class], T.Prov, T.ProvAmt, 

ISNULL(S.OverduePrin,0) as OverDuePrin, 

'PrinODDate' = S.PrinODDate,

'PrinODDays' = CASE WHEN DATEDIFF(DAY,S.PrinODDate,@Today)>=0 THEN DATEDIFF(DAY,S.PrinODDate,@Today) ELSE 0 END,

--'PrinODDays'= DATEDIFF(DAY,S.PrinODDate,@Today),

'LimitExpiryDays'= CASE WHEN DATEDIFF(DAY,T.[Limit Expiry Date],@Today) > 0 THEN DATEDIFF(DAY,T.[Limit Expiry Date],@Today) ELSE 0 END,

--'LimitExpiryDays'=DATEDIFF(DAY,T.[Limit Expiry Date],@Today),

--'OverDueInterest' = ISNULL(S.OverDueInterest,0), 

'OverDueInterest' = CASE WHEN ISNULL(S.OverDueInterest,0) > 0 THEN ISNULL(S.OverDueInterest,0)ELSE 0 END,

--'IntODDate' = S.IntODDate,

'IntODDate' = CASE WHEN DATEDIFF(DAY,S.IntODDate,@Today)>=0  AND ISNULL(S.OverDueInterest,0) > 0 THEN S.IntODDate ELSE @Today END,

--'IntOdDays' = DATEDIFF(DAY,S.IntODDate,@Today)

'IntOdDays' = CASE WHEN DATEDIFF(DAY,S.IntODDate,@Today)>=0  AND ISNULL(S.OverDueInterest,0) > 0 THEN DATEDIFF(DAY,S.IntODDate,@Today) ELSE 0 END

INTO #TEMP1

FROM #TEMP T 

FULL OUTER JOIN #TEMPOD S

ON T.MainCode = S.MainCode

WHERE T.MainCode IS NOT NULL

AND  PrinODDate BETWEEN '2019-01-01 00:00:00.000' AND '2020-01-01 00:00:00.000'

--SELECT * FROM #TEMP1 WHERE MainCode  = '00103400001443000004'

SELECT 

'Date' = ISNULL([PrinODDate],@Today), 

T.* 

INTO #TEMP2

FROM #TEMP1 T(NOLOCK)

--SELECT * FROM #TEMP2 WHERE MainCode  = '00103400001443000004'

SELECT 

BranchCode,Obligor,ClientCode,MainCode,Name,DisbursementDate,TypesOfCredit,AcType,ApprovedLimit,

[O/SPrincipal],[O/SInterest],OverDuePrincipal,[Limit Expiry Date],

--[Loan Class],[Prov],[ProvAmt],

OverDuePrin,

CONVERT(VARCHAR(15),[Date],103) [PrinODDate],

PrinODDays,LimitExpiryDays,

OverDueInterest,

CONVERT(VARCHAR(15),[Date],103) [IntODDate],

IntOdDays,

CONVERT(VARCHAR(15),[Date],103) [Date],

--,LoanClass_1,ProvAmt_1,

--T.*,

'LoanClass_1' = dbo.f_GetLoanClass1(BranchCode, MainCode,Date),

(CASE 

WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) IN ('Good','UnDue') THEN ROUND((CONVERT(MONEY,[O/SPrincipal]) * 0.01),2)

--WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) ='WatchList' THEN ROUND([O/SPrincipal] * 0.05,2)

--WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) ='Substandard' THEN ROUND([O/SPrincipal] *0.25,2)

--WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) ='Doubtful' THEN ROUND([O/SPrincipal] * 0.5,2)

WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) ='Doubtful' THEN ROUND([O/SPrincipal] * 0.35,2)

WHEN dbo.f_GetLoanClass1(BranchCode, MainCode,[Date]) ='Bad' THEN ROUND([O/SPrincipal],2) 

END) as ProvAmt_1,

'Sectorwise  Code C'  = (SELECT CustType + ' '+ CustDesc FROM v_AcCustType S(NOLOCK) WHERE CustTypeCode = 'C' AND S.MainCode = T.MainCode),

'Productwise Code K'  = (SELECT CustType + ' '+ CustDesc FROM v_AcCustType P(NOLOCK) WHERE CustTypeCode = 'K' AND P.MainCode = T.MainCode),

'NRB5 D'   = (SELECT CustType + ' '+ CustDesc FROM v_AcCustType P(NOLOCK) WHERE CustTypeCode = 'D' AND P.MainCode = T.MainCode),

'SME'       = (SELECT CustType + ' '+ CustDesc FROM v_AcCustType P(NOLOCK) WHERE CustTypeCode = 'K' AND CustType   = 'KC' AND P.MainCode = T.MainCode),

'Address' = (SELECT ISNULL(Address1,'') + ' ' + ISNULL(Address2,'') + ' '  + ISNULL(Address3,'') 

FROM ClientTable C(NOLOCK) WHERE C.ClientCode = T.ClientCode)               

INTO #TTT

FROM #TEMP2 T(NOLOCK)

--SELECT ROUND((CONVERT(MONEY,[O/SPrincipal])),2),* FROM #TTT where MainCode = '00103400001443000004'

SELECT 

BranchCode,Obligor,ClientCode,MainCode,Name,DisbursementDate,TypesOfCredit,AcType,

ApprovedLimit,[O/SPrincipal],[O/SInterest],OverDuePrincipal,[Limit Expiry Date],

OverDuePrin,PrinODDate,PrinODDays,LimitExpiryDays,

--OverDueInterest,

ISNULL(ROUND((CONVERT(MONEY,[OverDueInterest])),2),0) as OverDueInterest,

IntODDate,IntOdDays,

[Date],

LoanClass_1,

ISNULL(ROUND((CONVERT(MONEY,[ProvAmt_1])),2),0) as ProvAmt_1,

[Sectorwise  Code C],[Productwise Code K],[NRB5 D],SME,[Address] 

FROM #TTT

--where MainCode = '00103400001443000004'

UNION ALL

SELECT 

'~~~~~~', '~~~~~~','~~~~~~', '~~~~~~~~~', 'Grand Total', '','', '~~',

NULL,SUM([O/SPrincipal]), SUM([O/SInterest]), SUM(OverDuePrincipal),

NULL,

--'',NULL,SUM(ProvAmt),

SUM(OverDuePrin), NULL,NULL,NULL, 

--SUM(OverDueInterest), 

SUM(ISNULL(ROUND((CONVERT(MONEY,[OverDueInterest])),2),0)),

NULL, NULL,

NULL,

LoanClass_1= NULL,

SUM(ISNULL(ROUND((CONVERT(MONEY,[ProvAmt_1])),2),0)),

NULL,NULL,NULL,NULL,NULL             

FROM #TTT

--WHERE IntODDate BETWEEN '2019-01-01 00:00:00.000' AND '2020-01-01 00:00:00.000'

ORDER BY 1,8,4

DROP TABLE #TEMP, #TEMPOD, #TEMP1, #TEMP2,#TTT