From Fiery Cheetah, 6 Months ago, written in Text.
Embed
  1. USE [C_Lease]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[Destinity_LE_Select_FacilityFulArrearsDetails]    Script Date: 4/18/2018 4:37:18 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author:              <Naveen>
  10. -- Create date: <20-12-2016>
  11. -- Description: <Description,,>
  12. -- =============================================
  13.  
  14. --- [Destinity_LE_Select_FacilityFulArrearsDetails] '025171600001'
  15. ALTER PROCEDURE [dbo].[Destinity_LE_Select_FacilityFulArrearsDetails]
  16. (
  17.         @FacNo VARCHAR(50)
  18. )
  19.        
  20. AS
  21. BEGIN
  22.         SET NOCOUNT ON
  23.  
  24.     DECLARE @sErrorProcedure            VARCHAR(200),
  25.                         @sLog                                   VARCHAR(500)
  26.  
  27.  
  28.         CREATE TABLE #DueDetails
  29.                         (
  30.                         RefCode                         VARCHAR(2),
  31.                         ChargeType                      VARCHAR(100),
  32.                         TotalDueAmt                     MONEY,
  33.                         TotalSettledAmt         MONEY,
  34.                         TotalWaived                     MONEY,
  35.                         TotalOutstanding        MONEY
  36.                         )
  37.        
  38.         BEGIN TRY
  39.  
  40.                 INSERT INTO #DueDetails
  41.                 SELECT FS.RefCode,AC.Description As ChargeType,SUM(FS.Capital + FS.Interest) As TotalDueAmt,SUM(FS.Capital +FS.Interest) - SUM(FS.CapBalance + FS.IntBalance) As TotalSettledAmt,
  42.                 SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.CapBalance + FS.IntBalance) As TotalOutstanding
  43.                 FROM destinity_LE_FacShedule FS
  44.                 INNER JOIN destinity_LE_AddChargesRef AC ON FS.RefCode = AC.Code
  45.                 WHERE FacNo = @FacNo AND FS.Status NOT IN ('C','N','S','O') AND FS.RefCode <> 'RE'
  46.                 GROUP BY FS.RefCode,AC.Description
  47.  
  48.                 INSERT INTO #DueDetails
  49.                 SELECT FS.RefCode,'CAPITAL ARREARS' As ChargeType,SUM(FS.Capital) As TotalDueAmt,SUM(FS.Capital) - SUM(FS.CapBalance) As TotalSettledAmt,
  50.                 0 As TotalWaived,SUM(FS.CapBalance) As TotalOutstanding
  51.                 FROM destinity_LE_FacShedule FS
  52.                 WHERE FacNo = @FacNo AND FS.Status NOT IN ('C','N','S','O') AND FS.RefCode = 'RE'
  53.                 GROUP BY FS.RefCode
  54.  
  55.                 INSERT INTO #DueDetails
  56.                 SELECT FS.RefCode,'INTEREST ARREARS' As ChargeType,SUM(FS.Interest) As TotalDueAmt,SUM(FS.Interest) - SUM(FS.IntBalance) As TotalSettledAmt,
  57.                 0 As TotalWaived,SUM(FS.IntBalance) As TotalOutstanding
  58.                 FROM destinity_LE_FacShedule FS
  59.                 WHERE FacNo = @FacNo AND FS.Status NOT IN ('C','N','S','O') AND FS.RefCode = 'RE'
  60.                 GROUP BY FS.RefCode
  61.  
  62.                 --INSERT INTO #DueDetails
  63.                 --SELECT FS.RefCode,'RENTAL OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.OD) - SUM(FS.ODBalance) As TotalSettledAmt,
  64.                 --SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding
  65.                 --FROM destinity_LE_FacShedule FS
  66.                 --WHERE FacNo = @FacNo AND FS.RefCode = 'RE'
  67.                 --AND FS.Status NOT IN ('C','N','S','O')
  68.                 --GROUP BY FS.RefCode
  69.  
  70.                 INSERT INTO #DueDetails
  71.                 SELECT FS.RefCode,'RENTAL OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.ODBalance) - (SUM(FS.OD) - SUM(ISNULL(FS.waived,0))) As TotalSettledAmt,
  72.                 SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding
  73.                 FROM destinity_LE_FacShedule FS
  74.                 WHERE FacNo = @FacNo AND FS.RefCode = 'RE'
  75.                 AND FS.Status NOT IN ('C','N','S','O')
  76.                 GROUP BY FS.RefCode
  77.  
  78.  
  79.                 --INSERT INTO #DueDetails
  80.                 --SELECT FS.RefCode,'RENTAL VAT OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.OD) - SUM(FS.ODBalance) As TotalSettledAmt,
  81.                 --SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding
  82.                 --FROM destinity_LE_FacShedule FS
  83.                 --WHERE FacNo = @FacNo AND FS.RefCode = 'RV'
  84.                 --AND FS.Status NOT IN ('C','N','S','O')
  85.                 --GROUP BY FS.RefCode
  86.  
  87.                 INSERT INTO #DueDetails
  88.                 SELECT FS.RefCode,'RENTAL VAT OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.ODBalance) - (SUM(FS.OD) - SUM(ISNULL(FS.waived,0))) As TotalSettledAmt,
  89.                 SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding
  90.                 FROM destinity_LE_FacShedule FS
  91.                 WHERE FacNo = @FacNo AND FS.RefCode = 'RV'
  92.                 AND FS.Status NOT IN ('C','N','S','O')
  93.                 GROUP BY FS.RefCode
  94.  
  95.  
  96.                 --INSERT INTO #DueDetails
  97.                 --SELECT 'SD','SUNDRY OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.OD) - SUM(FS.ODBalance) As TotalSettledAmt,
  98.                 --SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding  
  99.                 --FROM destinity_LE_FacShedule FS
  100.                 --WHERE FacNo = @FacNo AND FS.RefCode NOT IN ('RE','RV','PP','02')
  101.                 --AND FS.Status NOT IN ('C','N','S','O')
  102.  
  103.                 INSERT INTO #DueDetails
  104.                 SELECT 'SD','SUNDRY OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.ODBalance) - (SUM(FS.OD) - SUM(ISNULL(FS.waived,0))) As TotalSettledAmt,
  105.                 SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding  
  106.                 FROM destinity_LE_FacShedule FS
  107.                 WHERE FacNo = @FacNo AND FS.RefCode NOT IN ('RE','RV','PP','02')
  108.                 AND FS.Status NOT IN ('C','N','S','O')
  109.  
  110.  
  111.                 --INSERT INTO #DueDetails
  112.                 --SELECT FS.RefCode,'INSURANCE OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.OD) - SUM(FS.ODBalance) As TotalSettledAmt,
  113.                 --SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding  
  114.                 --FROM destinity_LE_FacShedule FS
  115.                 --WHERE FacNo = @FacNo AND FS.RefCode = '02'
  116.                 --AND FS.Status NOT IN ('C','N','S','O')
  117.                 --GROUP BY FS.RefCode
  118.  
  119.                 INSERT INTO #DueDetails
  120.                 SELECT FS.RefCode,'INSURANCE OD' As ChargeType,SUM(FS.OD) As TotalDueAmt,SUM(FS.ODBalance) - (SUM(FS.OD) - SUM(ISNULL(FS.waived,0))) As TotalSettledAmt,
  121.                 SUM(ISNULL(FS.waived,0)) As TotalWaived,SUM(FS.ODBalance) As TotalOutstanding  
  122.                 FROM destinity_LE_FacShedule FS
  123.                 WHERE FacNo = @FacNo AND FS.RefCode = '02'
  124.                 AND FS.Status NOT IN ('C','N','S','O')
  125.                 GROUP BY FS.RefCode
  126.  
  127.                 INSERT INTO #DueDetails
  128.                 SELECT 'ZZ','TOTAL',SUM(TotalDueAmt),SUM(TotalSettledAmt),SUM(TotalWaived),SUM(TotalOutstanding) FROM #DueDetails
  129.  
  130.  
  131.                 SELECT DD.* FROM #DueDetails DD
  132.                 ORDER BY RefCode
  133.  
  134.                 DROP TABLE #DueDetails
  135.  
  136.  
  137.         END TRY
  138.         BEGIN CATCH
  139.                 DECLARE @iErrorNumber   INT,
  140.                                 @sErrorMessage  VARCHAR(1000)
  141.  
  142.                 SELECT  @sErrorProcedure=ERROR_PROCEDURE()
  143.                 SELECT  @sErrorMessage= ERROR_MESSAGE()
  144.                 SELECT  @iErrorNumber=ERROR_NUMBER()
  145.  
  146.                 RAISERROR (@sErrorMessage,16,1)
  147.  
  148.                 RETURN 0
  149.         END CATCH
  150.  
  151. END
  152.