USE [cwwebapp_nexnow]
GO

/****** Object:  StoredProcedure [dbo].[usp_report_AG_AgreementReconciliation]    Script Date: 3/17/2019 5:53:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*3421*/ CREATE PROCEDURE [dbo].[usp_report_AG_AgreementReconciliation]
(
     @Company_RecID NVARCHAR(20)
    ,@AGR_Header_RecID NVARCHAR(20)
    ,@StartDate NVARCHAR(20)
    ,@EndDate NVARCHAR(20)
    ,@DisplayNotes BIT
    ,@Date_Range NVARCHAR (5)
    ,@LANGUAGE NVARCHAR(6)
)
AS
BEGIN
    SET NOCOUNT ON;
     SET TRANSACTION isolation level READ uncommitted;

BEGIN TRY

    DECLARE @AGR_Header_RecID2 INT,
            @StartDate2 DATETIME,
            @EndDate2 DATETIME,
            @txt_Yes nvarchar(10),
            @txt_No nvarchar(10)

    SELECT @AGR_Header_RecID2 = CAST(@AGR_Header_RecID AS INT)
         ,@StartDate2 = CAST(@StartDate AS DATETIME)
         ,@EndDate2 = CAST(@EndDate AS DATETIME)

    SET  @txt_Yes = (SELECT dbo.udf_GetLocalizedText(@LANGUAGE,'Yes'))
    SET  @txt_No = (SELECT dbo.udf_GetLocalizedText(@LANGUAGE,'No'))

    IF @Date_Range = 'R'
        BEGIN
            SET @StartDate2 = CAST(@StartDate AS DATETIME)
            SET @EndDate2 = CAST(@EndDate + ' 23:59:59.000' AS DATETIME)
        END
    ELSE
        BEGIN
            SELECT @StartDate2 = StartDate, @EndDate2 = EndDate FROM [dbo].[udf_CalculateStartEndDateFromDateRange] (@Date_Range,NULL)
        END

    IF OBJECT_ID('tempdb..#AGREEMENT_RECON') IS NOT NULL
        DROP TABLE #AGREEMENT_RECON

    CREATE TABLE #AGREEMENT_RECON
    (
         ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
        ,rptDate DATETIME
        ,sortDate DATETIME
        ,agrAmt DECIMAL(18,2)
        ,typeDesc NVARCHAR(100)
        ,Comment NVARCHAR(MAX)
        ,Notes NVARCHAR(MAX)
    )

    INSERT INTO #AGREEMENT_RECON
    EXEC dbo.usp_report_AG_AGR_Recon @AGR_Header_RecID2, @StartDate2, @EndDate2, @LANGUAGE

    IF OBJECT_ID('tempdb..#AGREEMENT_BALANCE') IS NOT NULL
        DROP TABLE #AGREEMENT_BALANCE

    CREATE TABLE #AGREEMENT_BALANCE
    (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
        ,rptDate DATETIME
        ,sortDate DATETIME
        ,agrAmt DECIMAL(18,2)
        ,balanceAmt DECIMAL(18,2)
        ,typeDesc NVARCHAR(100)
        ,Comment NVARCHAR(MAX)
        ,Notes NVARCHAR(MAX)
    )

    DECLARE @RemainingBalance DECIMAL(18,2)
    SELECT @RemainingBalance = 0

    WHILE EXISTS(SELECT TOP 1 * FROM #AGREEMENT_RECON)
    BEGIN
        SELECT TOP 1 @RemainingBalance = @RemainingBalance + agrAmt FROM #AGREEMENT_RECON

        INSERT INTO #AGREEMENT_BALANCE
        SELECT TOP 1 rptDate, sortDate, agrAmt, @RemainingBalance, typeDesc, Comment, Notes
        FROM #AGREEMENT_RECON

        SET ROWCOUNT 1
        DELETE FROM #AGREEMENT_RECON
        SET ROWCOUNT 0
    END;

    WITH PARAMETERS AS
    (
        SELECT
             (SELECT Company_Name FROM dbo.Company WHERE Company_RecID = CAST(@Company_RecID AS INT)) AS Company
            ,(SELECT AGR_Name FROM dbo.AGR_Header WHERE AGR_Header_RecID = @AGR_Header_RecID) AS Agreement
            ,CASE @DisplayNotes WHEN 1 THEN @txt_Yes ELSE @txt_No END AS DisplayNotes
    )
    SELECT p.Company AS P_Company
        ,p.Agreement AS P_Agreement
        ,@StartDate2 AS P_StartDate
        ,@EndDate2 AS P_EndDate
        ,p.DisplayNotes AS P_DisplayNotes
        ,ab.rptDate
        ,ab.sortDate
        ,ab.agrAmt
        ,ab.balanceAmt
        ,ab.typeDesc
        ,ab.Comment
        ,ab.Notes
        ,CASE ah.AGR_Detail_Type_ID
            WHEN 'A' THEN dbo.udf_report_GetCurrencyFormatString(ah.Owner_Level_RecID, 2, 1, 0)
            ELSE '#,0.00;-#,0.00;0.00'
         END AS NumberFormatString
    FROM PARAMETERS p
        LEFT JOIN #AGREEMENT_BALANCE ab ON 1 = 1
        LEFT JOIN dbo.AGR_Header ah ON ah.AGR_Header_RecID = @AGR_Header_RecID2
    ORDER BY ID, ab.typeDesc

END TRY

BEGIN CATCH
     DECLARE
       @errNumber INT,
     @errMessage NVARCHAR(4000),
     @errSeverity INT,
     @errState INT,
     @errLine INT


     SET @errNumber = ERROR_NUMBER();
     SET @errMessage = ERROR_MESSAGE();
     SET @errSeverity = ERROR_SEVERITY();
     SET @errState = ERROR_STATE();
     SET @errLine = ERROR_LINE();

     INSERT INTO dbo.SQL_Procedure_Error(Stored_Procedure,Error_Number,Error_Severity,Error_Line,Error_Message)
     VALUES (OBJECT_NAME(@@PROCID),@errNumber,@errSeverity,@errLine,@errMessage);

END CATCH

END


GO