#1688

ALTER PROC USP_FI_ARAP_ReceiptReports
    @PerDate DATETIME = NULL,
    @ReportType VARCHAR(30) = '',
    @AllCustomers BIT = 1,
    @CustomerCodes VARCHAR(2000) = '',
    @AllCustomerAreas BIT = 1,
    @CustomerAreaIDs VARCHAR(2000) = '',
    @AllCustomerTypes BIT = 1,
    @CustomerTypeIDs VARCHAR(2000) = '',
    @AllARCollectors BIT = 1,
    @ARCollectorIDs VARCHAR(2000) = '',
    @StartDate DATETIME = NULL,
    @EndDate DATETIME = NULL,
    @ReportStartDate DATETIME = NULL,
    @ReportEndDate DATETIME = NULL,
    @AllocationStartDate DATETIME = NULL,
    @AllocationEndDate DATETIME = NULL,
    @ItemStartDate DATETIME = NULL,
    @ItemEndDate DATETIME = NULL,
    @SettleStartDate DATETIME = NULL,
    @SettleEndDate DATETIME = NULL,
    @IncomeOrExpense BIT = NULL,
    @CurrencyID INT = 0,
    @OverdueAfter INT = NULL,
    @BankAccountID INT = 0,
    @VoucherNumbers VARCHAR(8000) = NULL,
    @Class INT = 0,
    @Version VARCHAR(30) = '',
    @CreatedBy VARCHAR(30) = '',
    @PPhStartDate DATETIME = NULL,
    @PPhEndDate DATETIME = NULL
AS

SET NOCOUNT ON
DECLARE
    @SQL NVARCHAR(MAX),
    @ForITB BIT,
    @CMU BIT

SET @PPhStartDate = dbo.UDF_Common_FloorDate(@PPhStartDate)
SET @PPhEndDate = dbo.UDF_Common_FloorDate(@PPhEndDate)
SET @CMU = CASE WHEN @Version IN ('CMU', 'CALISPO') THEN 1 ELSE 0 END
SET @BankAccountID = ISNULL(@BankAccountID, 0)
SELECT @ForITB = dbo.UDF_SH_Settings_GetAsBit ('CustomizedForIT&B', 0)

IF @PerDate IS NOT NULL
    SET @PerDate = dbo.UDF_Common_FloorDate(@PerDate)

--  Create temporary tables
SET @StartDate = dbo.UDF_Common_FloorDate(@StartDate)
SET @EndDate = dbo.UDF_Common_FloorDate(@EndDate)
SET @ReportStartDate = dbo.UDF_Common_FloorDate(@ReportStartDate)
SET @ReportEndDate = dbo.UDF_Common_FloorDate(@ReportEndDate)
SET @AllocationStartDate = dbo.UDF_Common_FloorDate(@AllocationStartDate)
SET @AllocationEndDate = dbo.UDF_Common_FloorDate(@AllocationEndDate)
SET @ItemStartDate = dbo.UDF_Common_FloorDate(@ItemStartDate)
SET @ItemEndDate = dbo.UDF_Common_FloorDate(@ItemEndDate)
SET @SettleStartDate = dbo.UDF_Common_FloorDate(@SettleStartDate)
SET @SettleEndDate = dbo.UDF_Common_FloorDate(@SettleEndDate)
SET @CurrencyID = ISNULL(@CurrencyID, 0)
CREATE TABLE #SelectedMembers (MemberID INT)
CREATE TABLE #SelectedARCollectors (CollectorID INT)

--  Process requested customers
SET @SQL = 'INSERT #SelectedMembers (MemberID)'
SET @SQL = @SQL + CHAR(13) + 'SELECT CustomerID'
SET @SQL = @SQL + CHAR(13) + '  FROM AR_Customers C'
IF @AllCustomers != 1
    SET @SQL = @SQL + CHAR(13) + '  JOIN dbo.UDF_Common_Split(@CustomerCodes, '','') SelCust ON C.CustomerCode LIKE SelCust.Value'
IF @AllCustomerAreas != 1
    SET @SQL = @SQL + CHAR(13) + '  JOIN dbo.UDF_Common_Split(@CustomerAreaIDs, '','') SelArea ON ISNULL(C.CustomerAreaID, 0) = CAST(SelArea.Value AS INT)'
IF @AllCustomerTypes != 1
    SET @SQL = @SQL + CHAR(13) + '  JOIN dbo.UDF_Common_Split(@CustomerTypeIDs, '','') SelType ON ISNULL(C.CustomerTypeID, 0) = CAST(SelType.Value AS INT)'

EXEC SP_ExecuteSQL @SQL,
    N'@CustomerCodes VARCHAR(2000), @CustomerAreaIDs VARCHAR(2000), @CustomerTypeIDs VARCHAR(2000)',
    @CustomerCodes, @CustomerAreaIDs, @CustomerTypeIDs

--  Process requested AR Collectors
SET @SQL = 'INSERT #SelectedARCollectors (CollectorID)'
SET @SQL = @SQL + CHAR(13) + 'SELECT CollectorID'
SET @SQL = @SQL + CHAR(13) + '  FROM AR_Collectors S'
IF @AllARCollectors != 1
    SET @SQL = @SQL + CHAR(13) + '  JOIN dbo.UDF_Common_Split(@ARCollectorIDs, '','') SelSP ON S.SalesPersonCode LIKE SelSP.Value'

EXEC SP_ExecuteSQL @SQL,
    N'@ARCollectorIDs VARCHAR(2000)', @ARCollectorIDs

IF @ReportType = 'HMSReceipt' BEGIN
    SELECT M.VoucherNo [Voucher No], M.AssociateName [Guest Name], M.VoucherDate [Voucher DATE], M.AmountPaid [Amount Paid], M.PaymentMethod [Method],
           F.FolioNo [Folio NUMBER], F.FolioDate [Folio DATE], BA.BankAccountCode [Bank Account Code], M.CheckNo [CHECK/Card NUMBER], M.CheckDueDate [CHECK/Card Due DATE],
           M.BankName [Bank Name], CS.CustomerCode [Customer Code], CS.CustomerName [Customer Name]
      FROM FI_ARAP M
      LEFT JOIN #SelectedMembers C ON C.MemberID = M.AssociateID
      LEFT JOIN dbo.UDF_Common_Split (@VoucherNumbers, ',') VN ON M.VoucherNo LIKE VN.VALUE
      LEFT JOIN HMS_FO_FolioItems FI ON FI.SourceType = 'RV' AND FI.SourceID = M.VoucherID
      LEFT JOIN HMS_FO_Folios F ON F.FolioID = FI.FolioID
      LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = M.BankAccountID
      LEFT JOIN HMS_FO_Guests G ON G.GuestID = F.GuestID
      LEFT JOIN AR_Customers CS ON CS.CustomerID = G.BillToCustomerID
     WHERE M.IsAR = 1 AND (M.Void != 1 OR M.VoidCheck = 1) AND M.Class = @Class
       AND (@CreatedBy = '' OR M.CreatedBY = @CreatedBy)
       AND CASE WHEN (@StartDate IS NULL AND @EndDate IS NULL) OR (VoucherDate BETWEEN @StartDate AND @EndDate) THEN 1 ELSE 0 END = 1
         AND CASE WHEN (@ReportStartDate IS NULL AND @ReportEndDate IS NULL) OR (EDUReportDate BETWEEN @ReportStartDate AND @ReportEndDate) THEN 1 ELSE 0 END = 1
       AND (@BankAccountID = 0 OR M.BankAccountID = @BankAccountID)
       AND CASE WHEN @CurrencyID = 0 OR @CurrencyID = M.CurrencyID THEN 1 ELSE 0 END = 1
       AND (@VoucherNumbers IS NULL OR VN.VALUE IS NOT NULL)
     ORDER BY 3, 1
END ELSE IF @ReportType = 'HMSDeposit' BEGIN
    SELECT M.VoucherNo [Voucher No], M.AssociateName [Guest Name], M.VoucherDate [Voucher DATE], M.AmountPaid [Amount Paid], M.PaymentMethod [Method],
           F.FolioNo [Folio NUMBER], F.FolioDate [Folio DATE], BA.BankAccountCode [Bank Account Code], M.CheckNo [CHECK/Card NUMBER], M.CheckDueDate [CHECK/Card Due DATE],
           M.BankName [Bank Name], CS.CustomerCode [Customer Code], CS.CustomerName [Customer Name], RV.ReservationNo [Reservation NUMBER], RV.ReservationDate [Reservation DATE],
           RV.ArrivalDate [Arrival DATE], RV.DepartureDate [Departure DATE]
      FROM FI_ARAP M
      LEFT JOIN #SelectedMembers C ON C.MemberID = M.AssociateID
      LEFT JOIN dbo.UDF_Common_Split (@VoucherNumbers, ',') VN ON M.VoucherNo LIKE VN.VALUE
      LEFT JOIN HMS_FO_FolioItems FI ON FI.SourceType = 'DR' AND FI.SourceID = M.VoucherID AND FI.Void = 0
      LEFT JOIN HMS_FO_Folios F ON F.FolioID = FI.FolioID AND F.Void = 0
      LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = M.BankAccountID
      LEFT JOIN HMS_FO_Reservations RV ON RV.ReservationID = M.ReservationID
      LEFT JOIN HMS_FO_Guests G ON G.GuestID = M.RecordedGuestID
      LEFT JOIN AR_Customers CS ON CS.CustomerID = G.BillToCustomerID
     WHERE M.IsAR = 1 AND (M.Void != 1 OR M.VoidCheck = 1) AND M.Class = 2
       AND (@CreatedBy = '' OR M.CreatedBY = @CreatedBy)
       AND CASE WHEN (@StartDate IS NULL AND @EndDate IS NULL) OR (VoucherDate BETWEEN @StartDate AND @EndDate) THEN 1 ELSE 0 END = 1
         AND CASE WHEN (@ReportStartDate IS NULL AND @ReportEndDate IS NULL) OR (EDUReportDate BETWEEN @ReportStartDate AND @ReportEndDate) THEN 1 ELSE 0 END = 1
       AND (@BankAccountID = 0 OR M.BankAccountID = @BankAccountID)
       AND CASE WHEN @CurrencyID = 0 OR @CurrencyID = M.CurrencyID THEN 1 ELSE 0 END = 1
       AND (@VoucherNumbers IS NULL OR VN.VALUE IS NOT NULL)
     ORDER BY 3, 1
END ELSE IF @ReportType LIKE 'Receipt%' BEGIN
    SELECT M.VoucherID
      INTO #Summ
      FROM FI_ARAP M
      LEFT JOIN #SelectedMembers C ON C.MemberID = M.AssociateID
      LEFT JOIN AR_Collectors CL ON CL.CollectorID = M.CollectorID
      LEFT JOIN dbo.UDF_Common_Split (@VoucherNumbers, ',') VN ON M.VoucherNo LIKE VN.VALUE
     WHERE M.IsAR = 1 AND (M.Void != 1 OR M.VoidCheck = 1)  AND M.Class = @Class
       AND (@CreatedBy = '' OR M.CreatedBY = @CreatedBy)
       AND CASE WHEN M.CollectorID = 0 OR CL.CollectorID IS NOT NULL THEN 1 ELSE 0 END = 1
       AND CASE WHEN (@StartDate IS NULL AND @EndDate IS NULL) OR (VoucherDate BETWEEN @StartDate AND @EndDate) THEN 1 ELSE 0 END = 1
         AND CASE WHEN (@ReportStartDate IS NULL AND @ReportEndDate IS NULL) OR (EDUReportDate BETWEEN @ReportStartDate AND @ReportEndDate) THEN 1 ELSE 0 END = 1
       AND (@BankAccountID = 0 OR M.BankAccountID = @BankAccountID)
       AND CASE WHEN @IncomeOrExpense IS NULL OR @IncomeOrExpense = M.IncomeOrExpense THEN 1 ELSE 0 END = 1
       AND CASE WHEN @CurrencyID = 0 OR @CurrencyID = CurrencyID THEN 1 ELSE 0 END = 1
       AND (M.Class > 0 OR (M.IncomeOrExpense = 1 AND @AllCustomers = 1) OR C.MemberID IS NOT NULL)
       AND (@VoucherNumbers IS NULL OR VN.VALUE IS NOT NULL)
         AND (@PPhStartDate IS NULL OR @PPhEndDate IS NULL OR M.TglPPh BETWEEN @PPhStartDate AND @PPhEndDate)

    CREATE TABLE #Summary (
        [VoucherID] INT,
        [Voucher DATE] DATETIME,
        [Transfer DATE] DATETIME,
        [Voucher No.] VARCHAR(50),
        [Tax Invoice No.] VARCHAR(50),
        [Voucher TYPE] VARCHAR(50),
        [Voucher Class] VARCHAR(50),
        Currency VARCHAR(10),
        [Exchange Rate] MONEY,
        [Customer Code] VARCHAR(200),
        [Customer Name] VARCHAR(200),
        [Customer] VARCHAR(200),
        [Payment Method] VARCHAR(30),
        [Bank Account Code] VARCHAR(30),
        [CHECK NUMBER] VARCHAR(50),
        [CHECK Due DATE] DATETIME,
        [SOURCE Bank] VARCHAR(200),
        [SOURCE Account NUMBER] VARCHAR(100),
        [Total Amount Paid] MONEY,
        [Total Debit Note] MONEY,
        [Total Credit Note] MONEY,
        [Total Amount Allocated] MONEY,
        [Outstanding Down Payment] MONEY,
        [Closed Down Payment] MONEY,
        [Total Amount Paid (LOCAL)] MONEY,
        [Total Debit Note (LOCAL)] MONEY,
        [Total Credit Note (LOCAL)] MONEY,
        [Total Amount Allocated (LOCAL)] MONEY,
        [Outstanding Down Payment (LOCAL)] MONEY,
        [Exclude] VARCHAR(10),
        [Collector Name] VARCHAR(50),
        [Remarks] NTEXT,
        [Internal Remarks] NTEXT,
        [Clearing Memo No.] VARCHAR(50),
        [Clearing DATE] DATETIME,
        [Clearing Bank Account Code] VARCHAR(50),
        [Validated] BIT,
        [Validated BY] VARCHAR(30),
        [Validate DATE/TIME] DATETIME,
        [Card Settlement No.] VARCHAR(50),
        [Card Settlement DATE] DATETIME,
        [Transfer Conf. No.] VARCHAR(50),
        [Transfer Conf. DATE] DATETIME,
        [Transfer Conf. Account Code] VARCHAR(50),
        [Project Code] VARCHAR(50),
        [Project Name] VARCHAR(200),
        [Job GROUP] VARCHAR(200),
        [Job Code] VARCHAR(50),
        [Job Name] VARCHAR(200),
        [BQ NUMBER] VARCHAR(50),
        [Card TYPE] VARCHAR(50),
        [Card Terminal] VARCHAR(50))

    CREATE TABLE #Items (
        VoucherID INT,
        ItemType VARCHAR(30),
        ItemID INT,
        DocumentNo VARCHAR(50),
        DocumentDate DATETIME,
        DocumentDueDate DATETIME,
        AllocationDate DATETIME,
        Amount MONEY,
        IsPositive BIT,
        DocumentType VARCHAR(50),
        AmountPaid MONEY,
        AmountDebit MONEY,
        AmountCredit MONEY,
        DBNoteDescription VARCHAR(1000),
        DBNoteAccountCode VARCHAR(50),
        DBNoteAccountName VARCHAR(50),
        DBNoteDepartment VARCHAR(50),
        CRNoteDescription VARCHAR(1000),
        CRNoteAccountCode VARCHAR(50),
        CRNoteAccountName VARCHAR(50),
        CRNoteDepartment VARCHAR(50),
        EffectiveAmountPaid MONEY,
        ItemExchangeRate MONEY NOT NULL DEFAULT(1)
    )

    IF @ReportType LIKE '%Details' BEGIN
        EXEC ('
        ALTER TABLE #Summary ADD
          [ItemID] INT,
            ItemType VARCHAR(10),
          [Item Type] VARCHAR(500),
          [Item Ref] VARCHAR(500),
          [Item Date] DATETIME,
          [Item Due Date] DATETIME,
          [Item Exchange Rate] MONEY,
          [Settle Date] DATETIME,
          [Allocation Date] DATETIME,
          [Item Amount] MONEY,
          [Item Amount Paid] MONEY,
          [Item Debit Note] MONEY,
          [Item Credit Note] MONEY,
          [Item Effective Amount Paid] MONEY,
          [Item Amount Paid (Local)] MONEY,
          [Item Debit Note (Local)] MONEY,
          [Item Credit Note (Local)] MONEY,
          [Item Effective Amount Paid (Local)] MONEY,
            [DB Note Account Code] VARCHAR(50),
            [DB Note Account Name] VARCHAR(50),
            [DB Note Department] VARCHAR(50),
            [DB Note Description] VARCHAR(1000),
            [CR Note Account Code] VARCHAR(50),
            [CR Note Account Name] VARCHAR(50),
            [CR Note Department] VARCHAR(50),
            [CR Note Description] VARCHAR(1000),
            [Item Details] VARCHAR(4000)')

        INSERT #Items
        SELECT I.VoucherID, I.ItemType,
               I.ItemID, CAST('' AS VARCHAR(50)), CAST(NULL AS DATETIME), CAST(NULL AS DATETIME),
         I.AllocationDate, VAI.NetTotal, CL.IsPositive, '',
               CASE WHEN CL.IsPositive = 1 THEN 1 ELSE -1 END * I.AmountPaid AmountPaid,
               CASE WHEN CL.IsPositive = 1 THEN 1 ELSE -1 END * I.AmountDebit AmountDebit,
               CASE WHEN CL.IsPositive = 1 THEN 1 ELSE -1 END * I.AmountCredit AmountCredit,
               DebitNoteDescription, DB.AccountCode, DB.AccountName, DebitNoteDepartment,
               CreditNoteDescription, CR.AccountCode, CR.AccountName, CreditNoteDepartment,
               CASE WHEN CL.IsPositive = 1 THEN 1 ELSE -1 END * (I.AmountPaid+I.AmountCredit-I.AmountDebit), ISNULL(VAI.ExchangeRate, 1)
          FROM FI_ARAPItems I
          JOIN #Summ S ON S.VoucherID = I.VoucherID
          JOIN FI_ARAP M ON M.VoucherID = I.VoucherID
          JOIN SH_ARAPCalculations CL ON CL.CompCode = I.ItemType
          LEFT JOIN AC_Accounts DB ON DB.AccountID = I.DebitNoteAccountID
          LEFT JOIN AC_Accounts CR ON CR.AccountID = I.CreditNoteAccountID
          LEFT JOIN VIEW_ARAPItems VAI ON VAI.ItemID = I.ItemID AND VAI.CompCode = I.ItemType
         WHERE (@PerDate IS NULL OR M.VoucherDate <= @PerDate)
           AND (@PerDate IS NULL OR I.AllocationDate <= @PerDate)
             AND (@AllocationStartDate IS NULL OR @AllocationEndDate IS NULL OR I.AllocationDate BETWEEN @AllocationStartDate AND @AllocationEndDate)

        EXEC USP_FI_ARAP_ResolveItemNames @IncludeDueDate = 1

        DELETE #Items
       WHERE CASE WHEN (@ItemStartDate IS NULL AND @ItemEndDate IS NULL) OR (DocumentDate BETWEEN @ItemStartDate AND @ItemEndDate) THEN 1 ELSE 0 END = 0
    END

    EXEC ('
    ALTER TABLE #Summary ADD
        [Invoice Tag] VARCHAR(300),
        [Receipt Number] VARCHAR(300),
        [Receipt Date] DATETIME,
        [Settlement Info] VARCHAR(500),
        [Settlement Description] VARCHAR(100)')

    SELECT CL.ClearingID, CL.ClearingNumber, CL.ClearingDate, B.BankAccountCode, CD.VoucherID
      INTO #Clearing
      FROM FI_Clearing CL
      JOIN FI_ClearingDetails CD ON CD.ClearingID = CL.ClearingID
      JOIN #Summ S ON S.VoucherID = CD.VoucherID
      JOIN FI_BankAccounts B ON B.BankAccountID = CL.BankAccountID
     WHERE CL.Void != 1
       AND CASE WHEN @PerDate IS NULL OR CL.ClearingDate <= @PerDate THEN 1 ELSE 0 END = 1

    SELECT CL.TransferConfID, CL.TransferConfNumber, CL.TransferConfDate, B.BankAccountCode, CD.VoucherID
      INTO #TransferConf
      FROM FI_TransferConf CL
      JOIN FI_TransferConfDetails CD ON CD.TransferConfID = CL.TransferConfID
      JOIN #Summ S ON S.VoucherID = CD.VoucherID
      JOIN FI_BankAccounts B ON B.BankAccountID = CL.BankAccountID
     WHERE CL.Void != 1
       AND CASE WHEN @PerDate IS NULL OR CL.TransferConfDate <= @PerDate THEN 1 ELSE 0 END = 1

    SELECT CL.CardSettlementID, CL.CardSettlementNumber, CL.CardSettlementDate, B.BankAccountCode, CD.VoucherID
      INTO #CardSettlement
      FROM FI_CardSettlement CL
      JOIN FI_CardSettlementDetails CD ON CD.CardSettlementID = CL.CardSettlementID
      JOIN #Summ S ON S.VoucherID = CD.VoucherID
      JOIN FI_BankAccounts B ON B.BankAccountID = CL.BankAccountID
     WHERE CL.Void != 1
       AND CASE WHEN @PerDate IS NULL OR CL.CardSettlementDate <= @PerDate THEN 1 ELSE 0 END = 1

    SELECT I.VoucherID,
    SUM(VFlag*I.AmountPaid)AmountPaid,
    SUM(VFlag*I.AmountDebit)AmountDebit,
    SUM(VFlag*I.AmountCredit)AmountCredit,
    SUM(VFlag*(I.AmountPaid+I.AmountCredit-I.AmountDebit))EfCredit
    INTO #SMX
    FROM FI_ARAPItems I
    JOIN #Summ S ON S.VoucherID=I.VoucherID
    JOIN (SELECT *,CASE WHEN IsPositive=1 THEN 1 ELSE -1 END VFlag FROM SH_ARAPCalculations) CL ON CL.CompCode=I.ItemType
    WHERE (I.AllocationDate <= @PerDate OR @PerDate IS NULL)
    GROUP BY I.VoucherID
    CREATE INDEX IX1 ON #SMX (VoucherID)

    SELECT RI.ItemID, MAX(R.ReceiptNo) ReceiptNo, MAX(R.ReceiptDate) ReceiptDate
      INTO #Receipts
      FROM FI_Receipt R
      JOIN FI_ReceiptItems RI ON R.ReceiptID = RI.ReceiptID
     WHERE R.Void = 0 AND RI.ItemType IN ('SI','SR')
     GROUP BY RI.ItemID
    CREATE INDEX IX10 ON #Receipts (ItemID)

SELECT DISTINCT I.ItemType, I.ItemID, dbo.UDF_FI_ARAPNotes_GetDetails (N.ARAPNoteID) Details
  INTO #Details
  FROM #Items I
    JOIN FI_ARAPNotes N ON N.ARAPNoteID = I.ItemID AND I.ItemType LIKE '[RP][DN]'

    SET @SQL = '
INSERT #Summary
SELECT M.VoucherID,M.VoucherDate,M.TransferDate,M.VoucherNo,M.FPNo,M.VoucherType,CASE WHEN M.Class=1 THEN ''HMS Receipt Voucher'' WHEN M.Class=2 THEN''HMS Deposit Voucher''
WHEN M.IncomeOrExpense=1 THEN ''Income'' ELSE ''Receivable'' END,CR.Currency,M.ExchangeRate,
CS.CustomerCode,CASE WHEN M.IncomeOrExpense=1 THEN M.AssociateName ELSE CS.CustomerName END,
CASE WHEN M.IncomeOrExpense=1 THEN M.AssociateName ELSE dbo.UDF_Common_CombineCodeAndName(CS.CustomerCode,CS.CustomerName)END,
PaymentMethod,BA.BankAccountCode,M.CheckNo,M.CheckDueDate,M.BankName,M.AccountNo,M.AmountPaid,ISNULL(SM.AmountDebit,0),ISNULL(SM.AmountCredit,0),
CASE WHEN M.IncomeOrExpense=1 THEN M.AmountPaid ELSE ISNULL(SM.AmountPaid,0) END,
M.AmountPaid-CASE WHEN M.IncomeOrExpense=1 THEN M.AmountPaid ELSE ISNULL(SM.AmountPaid,0) END,0,
dbo.UDF_Common_ConvertRate(@CurrencyID,M.AmountPaid,M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,ISNULL(SM.AmountDebit,0),M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,ISNULL(SM.AmountCredit,0),M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,CASE WHEN M.IncomeOrExpense=1 THEN M.AmountPaid ELSE ISNULL(SM.AmountPaid,0) END,M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,M.AmountPaid-CASE WHEN M.IncomeOrExpense=1 THEN M.AmountPaid ELSE ISNULL(SM.AmountPaid,0) END, M.ExchangeRate),
CASE WHEN M.ExcludeInARAP=1 THEN''Exclude''ELSE''Include''END,ISNULL(CL.CollectorName,''''),
M.Remarks,M.InternalRemarks,CX.ClearingNumber,CX.ClearingDate,CX.BankAccountCode,
M.Verified,M.VerifiedBy,M.VerifyDateTime,CST.CardSettlementNumber,CST.CardSettlementDate,TF.TransferConfNumber,TF.TransferConfDate,TF.BankAccountCode,
NULL,NULL,NULL,NULL,NULL,NULL,CT.CardTypeDescription,CTM.CardTerminalDescription' +
CASE WHEN @ReportType LIKE '%Details' THEN ',
I.ItemID,I.ItemType,I.DocumentType,I.DocumentNo,I.DocumentDate,I.DocumentDueDate,I.ItemExchangeRate,
CASE WHEN M.PaymentMethod IN(''Cash'',''WriteOff'')THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND @CMU=1 THEN M.TransferDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=1 THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=0 THEN TF.TransferConfDate
WHEN M.PaymentMethod=''Card'' THEN CST.CardSettlementDate
WHEN M.PaymentMethod=''Check'' THEN COALESCE(CX.ClearingDate,M.CheckDueDate)
ELSE NULL END,I.AllocationDate,I.Amount,I.AmountPaid,I.AmountDebit,I.AmountCredit,I.EffectiveAmountPaid,
dbo.UDF_Common_ConvertRate(@CurrencyID,I.AmountPaid,M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,I.AmountDebit,M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,I.AmountCredit,M.ExchangeRate),
dbo.UDF_Common_ConvertRate(@CurrencyID,I.EffectiveAmountPaid,M.ExchangeRate),
DBNoteAccountCode,DBNoteAccountName,DBNoteDepartment,DBNoteDescription,CRNoteAccountCode,CRNoteAccountName,CRNoteDepartment,CRNoteDescription,
DTL.Details, IV.InvoiceTag,RCP.ReceiptNo,RCP.ReceiptDate' ELSE ',NULL,NULL,NULL' END + ', M.SettlementDescription,
COALESCE(RT.RevenueTypeDescription, ET.ExpenseTypeDescription) [Settlement Description]
FROM FI_ARAP M
LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 0
LEFT JOIN FI_ExpenseTypes ET ON ET.ExpenseTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 1
JOIN #Summ S ON S.VoucherID=M.VoucherID
JOIN CO_Currency CR ON CR.CurrencyID=M.CurrencyID
LEFT JOIN AR_Customers CS ON CS.CustomerID=M.AssociateID
LEFT JOIN #SelectedMembers SCS ON SCS.MemberID = CS.CustomerID
LEFT JOIN AR_Collectors CL ON CL.CollectorID=M.CollectorID
LEFT JOIN FI_CardTypes CT ON CT.CardTypeID=M.CardTypeID
LEFT JOIN FI_CardTerminals CTM ON CTM.CardTerminalID=M.CardTerminalID
LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID=M.BankAccountID
LEFT JOIN #SMX SM ON SM.VoucherID=M.VoucherID
LEFT JOIN #Clearing CX ON CX.VoucherID=M.VoucherID
LEFT JOIN #TransferConf TF ON TF.VoucherID=M.VoucherID
LEFT JOIN #CardSettlement CST ON CST.VoucherID=M.VoucherID' +
CASE WHEN @ReportType LIKE '%Details' THEN '
JOIN #Items I ON I.VoucherID = M.VoucherID
LEFT JOIN #Details DTL ON DTL.ItemType = I.ItemType AND DTL.ItemID = I.ItemID
LEFT JOIN AR_Invoices IV ON IV.InvoiceID = I.ItemID AND I.ItemType IN (''SI'',''SR'')
LEFT JOIN #Receipts RCP ON RCP.ItemID=IV.InvoiceID' ELSE '' END + '
WHERE (@BankAccountID=0 OR M.BankAccountID=@BankAccountID)
  AND (@PerDate IS NULL OR M.VoucherDate<=@PerDate)' +
CASE WHEN @SettleStartDate IS NULL AND @SettleEndDate IS NULL THEN '' ELSE '
AND ((M.PaymentMethod=''Check'' AND CX.ClearingDate BETWEEN @StStartDate AND @StEndDate)
OR (M.PaymentMethod=''Card'' AND CST.CardSettlementDate BETWEEN @StStartDate AND @StEndDate)
OR (M.PaymentMethod IN (''Cash'',''WriteOff'') AND M.VoucherDate BETWEEN @StStartDate AND @StEndDate)
OR (M.PaymentMethod=''Transfer'' AND CASE WHEN M.Transferred = 1 THEN M.VoucherDate ELSE TF.TransferConfDate END BETWEEN @StStartDate AND @StEndDate)
OR (M.PaymentMethod NOT IN(''Cash'',''WriteOf'',''Check'',''Card'',''Transfer'')))' END + '
ORDER BY 2,4'

    EXEC SP_ExecuteSQL @SQL, N'@CurrencyID INT, @BankAccountID INT, @StStartDate DATETIME, @StEndDate DATETIME, @PerDate DATETIME, @CMU BIT',
        @CurrencyID, @BankAccountID, @SettleStartDate, @SettleEndDate, @PerDate, @CMU

UPDATE #Summary
   SET [Closed Down Payment] = V.Closed
  FROM #Summary SX
  JOIN (
/*SELECT V.VoucherID, SUM(N.Amount) Closed
  FROM FI_ARAP V
  JOIN #Summ S ON S.VoucherID = V.VoucherID
  JOIN FI_ARAPNotes N ON N.SourceVoucherID = V.VoucherID
 WHERE (N.Date <= @PerDate OR @PerDate IS NULL)
 GROUP BY V.VoucherID*/
SELECT V.VoucherID, SUM(CASE WHEN N.Class = 0 THEN 1 ELSE -1 END * VI.AmountPaid) Closed
  FROM FI_ARAP V
  JOIN #Summ S ON S.VoucherID = V.VoucherID
  JOIN FI_ARAPItems VI ON VI.VoucherID = V.VoucherID
  JOIN FI_ARAPNotes N ON N.ARAPNoteID = VI.ItemID
  JOIN FI_ARAP X ON X.VoucherID = N.SourceVoucherID
 WHERE VI.ItemType IN ('RN','RD','PN','PD') AND VI.AllocationDate <= @PerDate
 GROUP BY V.VoucherID) V
    ON V.VoucherID = SX.VoucherID

IF @ReportType LIKE '%Details' BEGIN
    --  Revenues
    SET @SQL = '
INSERT #Summary
SELECT M.VoucherID, M.VoucherDate, M.TransferDate,M.VoucherNo, M.FPNo[Tax Invoice No.],M.VoucherType, CASE WHEN M.IncomeOrExpense = 1 THEN CASE WHEN IsAR = 1 THEN ''Income'' ELSE ''Expense'' END ELSE ''Payable'' END, CR.Currency, M.ExchangeRate,
M.AssociateName,M.AssociateName,M.AssociateName,
PaymentMethod, BA.BankAccountCode, M.CheckNo, M.CheckDueDate, M.BankName, M.AccountNo,
M.AmountPaid,0,0,M.AmountPaid,0,0,
dbo.UDF_Common_ConvertRate(@CurrencyID, M.AmountPaid, M.ExchangeRate),0,0,
dbo.UDF_Common_ConvertRate(@CurrencyID, M.AmountPaid, M.ExchangeRate),0,
CASE WHEN M.ExcludeInARAP = 1 THEN ''Exclude'' ELSE ''Include'' END,
CL.CollectorName,M.Remarks,M.InternalRemarks,ISNULL(CLR.ClearingNumber, ''''), CLR.ClearingDate, ISNULL(CLR.BankAccountCode, ''''),
M.Verified, M.VerifiedBy, M.VerifyDateTime, CST.CardSettlementNumber, CST.CardSettlementDate, TF.TransferConfNumber, TF.TransferConfDate, TF.BankAccountCode,NULL,NULL,NULL,NULL,NULL,NULL,
CT.CardTypeDescription,CTM.CardTerminalDescription' +
CASE WHEN @ReportType LIKE '%Details' THEN ',
SM.VoucherExtraItemID,'''','''',RIGHT(CAST(COALESCE(ET.ExpenseTypeDescription,RT.RevenueTypeDescription,SM.Description) AS VARCHAR(500)),200),NULL,NULL,NULL,NULL,
CASE WHEN M.PaymentMethod IN (''Cash'') THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=1 THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=0 THEN TF.TransferConfDate
WHEN M.PaymentMethod=''Check'' THEN CLR.ClearingDate
ELSE NULL END,SM.Amount,
SM.Amount,0,0,SM.Amount,
dbo.UDF_Common_ConvertRate(@CurrencyID, SM.Amount, M.ExchangeRate),
0,0,dbo.UDF_Common_ConvertRate(@CurrencyID, SM.Amount, M.ExchangeRate),
'''','''', '''','''','''', '''', '''', '''', COALESCE(dbo.UDF_Common_NullIfEmpty(SM.Description), ET.ExpenseTypeDescription, RT.RevenueTypeDescription), NULL, NULL, NULL' ELSE '' END + ', M.SettlementDescription,
COALESCE(WRT.RevenueTypeDescription, WET.ExpenseTypeDescription) [Settlement Description]
FROM FI_ARAP M
LEFT JOIN FI_RevenueTypes WRT ON WRT.RevenueTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 0
LEFT JOIN FI_ExpenseTypes WET ON WET.ExpenseTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 1
JOIN #Summ S ON S.VoucherID = M.VoucherID
LEFT JOIN CC_BQ BQ ON BQ.BQID=M.BQID
LEFT JOIN AR_Collectors CL ON CL.CollectorID=M.CollectorID
JOIN CO_Currency CR ON CR.CurrencyID = M.CurrencyID
LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = M.BankAccountID
JOIN FI_ARAPExtraItems SM ON SM.VoucherID = M.VoucherID
LEFT JOIN #Clearing CLR ON CLR.VoucherID = M.VoucherID
LEFT JOIN #TransferConf TF ON TF.VoucherID = M.VoucherID
LEFT JOIN #CardSettlement CST ON CST.VoucherID = M.VoucherID
LEFT JOIN FI_ExpenseTypes ET ON ET.ExpenseTypeID = SM.TypeID AND M.IsAR = 0
LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = SM.TypeID AND M.IsAR = 1
LEFT JOIN FI_CardTypes CT ON CT.CardTypeID=M.CardTypeID
LEFT JOIN FI_CardTerminals CTM ON CTM.CardTerminalID=M.CardTerminalID
WHERE M.IncomeOrExpense = 1
AND (@ALlocationStartDate iS NULL OR @AllocationEndDate IS NULL OR M.VoucherDate BETWEEN @AllocationStartDate AND @AllocationEndDate)
AND (@ItemStartDate IS NULL OR @ItemEndDate IS NULL OR M.VoucherDate BETWEEN @ItemstartDate AND @ItemEndDate)' +
CASE WHEN @SettleStartDate IS NULL AND @SettleEndDate IS NULL THEN '' ELSE '
AND ((M.PaymentMethod=''Check'' AND CLR.ClearingDate BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod=''Transfer'' AND CASE WHEN M.Transferred = 1 THEN M.VoucherDate ELSE TF.TransferConfDate END BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod IN (''WriteOff'',''Cash'') AND M.VoucherDate BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod NOT IN(''Check'',''Cash'',''WriteOff'',''Transfer'')))' END + '
ORDER BY M.VoucherDate, M.VoucherNo'

    EXEC SP_ExecuteSQL @SQL, N'@CurrencyID INT, @PerDate DATETIME,  @SettleStartDate DATETIME, @SettleEndDate DATETIME,
    @ItemStartDate DATETIME, @ItemEndDate DATETIME, @AllocationStartDate DATETIME, @AllocationEndDate DATETIME',
        @CurrencyID, @PerDate, @SettleStartDate, @SettleEndDate, @ItemStartDate, @ItemEndDate, @AllocationStartDate, @AllocationEndDate

--  Other Items
    SET @SQL = '
INSERT #Summary
SELECT M.VoucherID, M.VoucherDate, M.TransferDate,M.VoucherNo, M.FPNo[Tax Invoice No.],M.VoucherType, CASE WHEN M.IncomeOrExpense = 1 THEN CASE WHEN IsAR = 1 THEN ''Income'' ELSE ''Expense'' END ELSE ''Payable'' END, CR.Currency, M.ExchangeRate,
M.AssociateName,M.AssociateName,M.AssociateName,
PaymentMethod, BA.BankAccountCode, M.CheckNo, M.CheckDueDate, M.BankName, M.AccountNo,
M.AmountPaid,0,0,M.AmountPaid,0,0,
dbo.UDF_Common_ConvertRate(@CurrencyID, M.AmountPaid, M.ExchangeRate),0,0,
dbo.UDF_Common_ConvertRate(@CurrencyID, M.AmountPaid, M.ExchangeRate),0,
CASE WHEN M.ExcludeInARAP = 1 THEN ''Exclude'' ELSE ''Include'' END,
CL.CollectorName,M.Remarks,M.InternalRemarks,ISNULL(CLR.ClearingNumber, ''''), CLR.ClearingDate, ISNULL(CLR.BankAccountCode, ''''),
M.Verified, M.VerifiedBy, M.VerifyDateTime, CST.CardSettlementNumber, CST.CardSettlementDate, TF.TransferConfNumber, TF.TransferConfDate, TF.BankAccountCode,NULL,NULL,NULL,NULL,NULL,NULL,
CT.CardTypeDescription,CTM.CardTerminalDescription' +
CASE WHEN @ReportType LIKE '%Details' THEN ',
0,'''','''',RIGHT(CAST(COALESCE(SM.Description, NULL) AS VARCHAR(500)),200),NULL,NULL,NULL,NULL,
CASE WHEN M.PaymentMethod IN (''Cash'') THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=1 THEN M.VoucherDate
WHEN M.PaymentMethod=''Transfer'' AND M.Transferred=0 THEN TF.TransferConfDate
WHEN M.PaymentMethod=''Check'' THEN CLR.ClearingDate
ELSE NULL END,SM.Amount,
SM.Amount,0,0,SM.Amount,
dbo.UDF_Common_ConvertRate(@CurrencyID, SM.Amount, M.ExchangeRate),
0,0,dbo.UDF_Common_ConvertRate(@CurrencyID, SM.Amount, M.ExchangeRate),
'''','''', '''','''','''', '''', '''', '''', COALESCE(dbo.UDF_Common_NullIfEmpty(SM.Description), NULL), NULL, NULL, NULL' ELSE '' END + ', M.SettlementDescription,
COALESCE(WRT.RevenueTypeDescription, WET.ExpenseTypeDescription) [Settlement Description]
FROM FI_ARAP M
LEFT JOIN FI_RevenueTypes WRT ON WRT.RevenueTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 0
LEFT JOIN FI_ExpenseTypes WET ON WET.ExpenseTypeID = M.ExpenseOrRevenueTypeID AND M.IsAR = 1
JOIN #Summ S ON S.VoucherID = M.VoucherID
LEFT JOIN CC_BQ BQ ON BQ.BQID=M.BQID
LEFT JOIN AR_Collectors CL ON CL.CollectorID=M.CollectorID
JOIN CO_Currency CR ON CR.CurrencyID = M.CurrencyID
LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = M.BankAccountID
JOIN VIEW_FI_ARAP_OtherItems SM ON SM.VoucherID = M.VoucherID
LEFT JOIN #Clearing CLR ON CLR.VoucherID = M.VoucherID
LEFT JOIN #TransferConf TF ON TF.VoucherID = M.VoucherID
LEFT JOIN #CardSettlement CST ON CST.VoucherID = M.VoucherID
LEFT JOIN FI_CardTypes CT ON CT.CardTypeID=M.CardTypeID
LEFT JOIN FI_CardTerminals CTM ON CTM.CardTerminalID=M.CardTerminalID
WHERE (@ALlocationStartDate iS NULL OR @AllocationEndDate IS NULL OR M.VoucherDate BETWEEN @AllocationStartDate AND @AllocationEndDate)
AND (@ItemStartDate IS NULL OR @ItemEndDate IS NULL OR M.VoucherDate BETWEEN @ItemstartDate AND @ItemEndDate)' +
CASE WHEN @SettleStartDate IS NULL AND @SettleEndDate IS NULL THEN '' ELSE '
AND ((M.PaymentMethod=''Check'' AND CLR.ClearingDate BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod=''Transfer'' AND CASE WHEN M.Transferred = 1 THEN M.VoucherDate ELSE TF.TransferConfDate END BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod IN (''WriteOff'',''Cash'') AND M.VoucherDate BETWEEN @SettleStartDate AND @SettleEndDate)
OR (M.PaymentMethod NOT IN(''Check'',''Cash'',''WriteOff'',''Transfer'')))' END + '
ORDER BY M.VoucherDate, M.VoucherNo'

    EXEC SP_ExecuteSQL @SQL, N'@CurrencyID INT, @PerDate DATETIME,  @SettleStartDate DATETIME, @SettleEndDate DATETIME,
    @ItemStartDate DATETIME, @ItemEndDate DATETIME, @AllocationStartDate DATETIME, @AllocationEndDate DATETIME',
        @CurrencyID, @PerDate, @SettleStartDate, @SettleEndDate, @ItemStartDate, @ItemEndDate, @AllocationStartDate, @AllocationEndDate

IF @Version = 'EDU' BEGIN
SELECT S.*, M.MajorName [EDU Major], L.LevelName [EDU Level], L.LevelCode [EDU Level Code],
       CN.ClassName [EDU Class],V.EDUReportDate [EDU Report DATE]
  FROM #Summary S
    JOIN FI_ARAP V ON V.VoucherID = S.VoucherID
    LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (V.AssociateID, V.VoucherDate)
    LEFT JOIN EDU_Majors M ON M.MajorID = SC.MajorID
    LEFT JOIN EDU_Level L ON L.LeveLID = SC.LevelID
    LEFT JOIN EDU_ClassName CN ON CN.ClassNameID = SC.ClassID
RETURN
END
END

UPDATE #Summary
   SET [Project Code] = P.ProjectCode, [Project Name] = P.ProjectName, [BQ NUMBER] = BQ.BQNumber, [Job Code] = J.JobCode, [Job Name] = J.JobName, [Job GROUP] = G.JobGroupName
  FROM #Summary S
  JOIN FI_ARAP V ON V.VoucherID = S.VoucherID
  LEFT JOIN CC_BQ BQ ON BQ.BQID = V.BQID
  LEFT JOIN CC_Jobs J ON J.JobID = BQ.JobID
  LEFT JOIN CC_JobGroups G ON G.JobGroupID = J.JobGroupID
  LEFT JOIN CC_Projects P ON P.ProjectID = G.ProjectID

    IF dbo.UDF_SH_Settings_GetAsBit ('ForHTS', 0) = 1 AND @ReportType = 'ReceiptSummary' BEGIN
        SET @SQL = '
        INSERT #Summary
        SELECT 0, N.Date, N.Number, '''', ''Receivable'', CR.Currency, N.ExchangeRate, CS.CustomerCode, CS.CustomerName, dbo.UDF_Common_CombineCodeAndName (CS.CustomerCode, CS.CustomerName),
               ''Cash'', BA.BankAccountCode, '''', NULL, '''', '''', CASE WHEN N.Class = 0 THEN -1 ELSE 1 END * D.Amount, 0, 0, CASE WHEN N.Class = 0 THEN -1 ELSE 1 END * D.Amount, 0,
               dbo.UDF_Common_ConvertRate (0, CASE WHEN N.Class = 0 THEN -1 ELSE 1 END * D.Amount, N.ExchangeRate), 0, 0, dbo.UDF_Common_ConvertRate (0, CASE WHEN N.Class = 0 THEN -1 ELSE 1 END * D.Amount, N.ExchangeRate),
               0, ''Include'', '''', '''', '''', NULL, '''', 0, '''', NULL, '''', NULL, '''', NULL, ''''
          FROM FI_ARAPNotes N
          JOIN CO_Currency CR ON CR.CurrencyID = N.CurrencyID
          JOIN AR_Customers CS ON CS.CustomerID = N.AssociateID
          JOIN FI_ARAPNoteDetails D ON D.ARAPNoteID = N.ARAPNoteID
          JOIN FI_BankAccounts BA ON BA.BankAccountID = D.BankAccountID
         WHERE N.IsAR = 1 AND N.Void != 1 AND N.Date BETWEEN @StartDate AND @EndDate AND D.LinkedToBankAccount = 1'
        EXEC SP_ExecuteSQL @SQL, N'@StartDate DATETIME, @EndDate DATETIME', @StartDate, @EndDate
    END

    IF @ForITB != 1 BEGIN
        IF @ReportType LIKE '%Details' BEGIN
            SET @SQL = '
SELECT RI.ItemType, RI.ItemID, MAX(R.ReceiptNo) ReceiptNo, MAX(R.ReceiptDate) ReceiptDate
    INTO #XReceipts
    FROM FI_Receipt R
    JOIN FI_ReceiptItems RI ON R.ReceiptID = RI.ReceiptID
    WHERE R.Void = 0
    GROUP BY RI.ItemType, RI.ItemID
CREATE INDEX IX10 ON #XReceipts (ItemType, ItemID)

SELECT S.*, V.ExternalRef Ref, CASE WHEN [Payment Method] = ''Cash'' THEN [Voucher Date]
               WHEN [Payment Method] = ''Check'' THEN [Clearing Date]
               WHEN [Payment Method] = ''Transfer'' AND (Transferred = 1 OR IncomingTransferVerified = 1) THEN [Transfer Conf. Date]
               WHEN [Payment Method] = ''Transfer'' THEN [Transfer Conf. Date]
               WHEN [Payment Method] = ''Card'' THEN [Card Settlement Date]
           ELSE [Voucher Date]
               END [Effective Date], V.BankCharges[Bank Charges],
       CAST(dbo.UDF_Common_MinusToZero(DATEDIFF (D, [Item Date], ' + CASE WHEN @CMU = 1 THEN 'CASE WHEN V.SRDate IS NOT NULL THEN V.SRDate
WHEN V.PaymentMethod = ''Cash'' THEN V.VoucherDate WHEN V.PaymentMethod = ''Transfer'' THEN V.TransferDate ELSE V.CheckDueDate END' ELSE '[Settle Date]' END + ')) AS INT) [Actual Payment Terms],
       CAST(dbo.UDF_Common_MinusToZero(DATEDIFF (D, [Item Due Date], CASE WHEN @CMU = 1 AND V.SRDate IS NOT NULL THEN V.SRDate WHEN V.PaymentMethod=''Transfer'' AND @CMU = 1 THEN V.TransferDate
WHEN V.PaymentMethod=''Check'' AND @CMU = 1 THEN V.CheckDueDate ELSE [Settle Date] END)) AS INT)
       [Overdue Days],DO.DeliveryOrderNumber [Delivery Order Number],
       IV.BLDate [BL Date], ISNULL(COALESCE(IV.CustomerPOs, DO.DeliveryOrderNumber), '''') [Customer PO], SP.SalesPersonCode [Customer''s Sales Person Code], SP.SalesPersonName [Customer''s Sales Person Name],
             CT.ContractNumber [Contract Number],
       ISP.SalesPersonCode [Invoice''s Sales Person Code], ISP.SalesPersonName [Invoice''s Sales Person Name],
       CASE WHEN V.PaymentMethod != ''Check'' THEN '''' WHEN V.OtherOwnerCheck=1 THEN ''No'' ELSE ''Yes'' END [Owner Check], IV.InvoiceType [Invoice Type],
       COALESCE(ARN.Remarks, IV.Remarks) [Item Remarks], V.NoPPh [No. Bukti Potong PPh], V.DeductedPPh [Pot. PPh], V.TglPPh [Tanggal Bukti Potong PPh],
       CA.CustomerAreaCode [Customer Area Code], CA.CustomerAreaName [Customer Area Name], CSA.CustomerSubAreaCode [Customer Sub-Area Code], CSA.CustomerSubAreaName [Customer Sub-Area Name],
       CASE WHEN V.OtherOwnerCheck = 1 THEN ''Other'' ELSE ''Own'' END [Check Owner],
       ISNULL(AC.AccountCode, '''') [Account Code], ISNULL(AC.AccountName, '''') [Account Name], ISNULL(COALESCE(dbo.UDF_Common_NullIfEmpty(EXI.Department), RT.Department), '''') [Cost Center],CCN.Description[Cost Center Description],
       ISNULL(LV.LevelName, '''') [EDU Level], CN.ClassName [EDU Class Name], M.MajorName [EDU Major], --ERT.RevenueTypeDescription [EDU Revenue Type],
         IV.FPNo [Item Tax Invoice No.],
         XR.receiptdate [Invoice Receipt Date],
       CASE WHEN V.VoidCheck=1 THEN ''Yes'' ELSE '''' END [Void Check],
                   BG.BusinessGroupName [Business Group Code], BG.Description [Business Group Name], V.CreatedBy [Created By], V.CreateDate [Create Date], CF.Description [Cash Flow Code], V.NetAmountPaid [Total Net Amount Paid],
                   CASE WHEN S.[Item Type] IN (''AR Sales'', ''AR Sales Return'') THEN dbo.UDF_AR_Invoices_GetDescriptionWithAcc (S.ItemID)
           ELSE EXI.DEscription END [Item Description]' + CASE WHEN @Version NOT IN ('MBP') THEN '' ELSE ',
       dbo.UDF_AR_Invoices_GetSODates (IV.InvoiceID, ''SODate'') [SO Date],
       dbo.UDF_AR_Invoices_GetSONumber (IV.InvoiceID) [SO Number]' END + '
  INTO #XDays
  FROM #Summary S
    LEFT JOIN #XReceipts XR ON XR.ItemType = S.ItemType AND XR.ItemID = S.ItemID
  LEFT JOIN FI_ARAP V ON V.VoucherID = S.VoucherID
  LEFT JOIN FI_CashFlow CF ON CF.ID = V.CashFlowID
  LEFT JOIN AR_Invoices IV ON IV.InvoiceID = S.ItemID AND S.[Item Type] IN (''AR Sales'', ''AR Sales Return'')
  LEFT JOIN FI_ARAPNotes ARN ON ARN.ARAPNoteID = S.ItemID AND S.[Item Type] IN (SELECT DisplayTitle FROM SH_ARAPCalculations WHERE SourceTable = ''FI_ARAPNotes'')
  LEFT JOIN AR_Customers C ON C.CustomerCode = S.[Customer Code]
  LEFT JOIN AR_BUsinessGroups BG ON BG.BusinessGroupID = C.BusinessGroupID
  LEFT JOIN AR_CustomerAreas CA ON CA.CustomerAreaID = C.CustomerAreaID
  LEFT JOIN AR_CustomerSubAreas CSA ON CSA.CustomerSubAreaID = C.CustomerSubAreaID
  LEFT JOIN AR_SalesPersons SP ON SP.SalesPersonID = C.SalesPersonID
  LEFT JOIN AR_SalesPersons ISP ON ISP.SalesPersonID = COALESCE(IV.SalesPersonID, ARN.SalesPersonID)
  LEFT JOIN PALM_Tickets T ON T.TicketID = S.ItemID AND S.[Item Type] = ''Pengiriman Produk''
  LEFT JOIN PALM_DeliveryOrders DO ON DO.DeliveryOrderID = T.DeliveryOrderID
    LEFT JOIN PALM_Contracts CT ON CT.ContractID = DO.ContractID
  LEFT JOIN FI_ARAPExtraItems EXI ON EXI.VoucherExtraItemID = S.ItemID AND V.IncomeOrExpense = 1
  LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = EXI.TypeID AND V.IsAR = 1
  LEFT JOIN AC_Accounts AC ON AC.AccountID = COALESCE(CASE WHEN EXI.AccountID = 0 THEN NULL ELSE EXI.AccountID END, RT.AccountID)
  LEFT JOIN AC_CostCenterNames CCN ON CCN.CostCenter = ISNULL(COALESCE(dbo.UDF_Common_NullIfEmpty(EXI.Department), RT.Department), '''')
      LEFT JOIN EDU_StatusChange SCX ON SCX.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (C.CustomerID, V.VoucherDate)
      LEFT JOIN EDU_Level LV ON LV.LevelID = SCX.LevelID
      LEFT JOIN EDU_ClassName CN ON CN.ClassnameID = SCX.ClassID
      LEFT JOIN EDU_Majors M ON M.MajorID = SCX.MajorID
 WHERE (@OverdueAfter IS NULL OR CAST(dbo.UDF_Common_MinusToZero(DATEDIFF (D, [Item Due Date], [Settle Date])) AS INT) >= @OverdueAfter)

SELECT XD.*,
       ISNULL(DATEDIFF(DD, [Voucher Date], ISNULL([Settle Date], [Voucher Date])), 0) [Voucher-Settle Days],
       ISNULL(DATEDIFF(DD, [Voucher Date], [Receipt Date]), 0) [Voucher-Receipt Days],
       ISNULL(DATEDIFF(DD, [Receipt Date], ISNULL([Settle Date], [Voucher Date])), 0) [Receipt-Settle Days],
       ISNULL(DATEDIFF(DD, [Item Date], ISNULL([Settle Date], [Voucher Date])), 0) [Invoice-Settle Date Days],
             DATEDIFF (DAY, [Item Date], [Item Due Date]) [Terms]
  FROM #XDays XD'
        END ELSE BEGIN
            SET @SQL = '
            SELECT S.*, V.ExternalRef Ref, CASE WHEN [Payment Method] = ''Cash'' THEN [Voucher Date]
                           WHEN [Payment Method] = ''Check'' THEN [Clearing Date]
                           WHEN [Payment Method] = ''Transfer'' AND (Transferred = 1 OR IncomingTransferVerified = 1) THEN [Transfer Conf. Date]
                           WHEN [Payment Method] = ''Transfer'' THEN [Transfer Conf. Date]
                          WHEN [Payment Method] = ''Card'' THEN [Card Settlement Date]
              ELSE [Voucher Date]
                           END [Effective Date], SP.SalesPersonCode [Customer''s Sales Person Code], SP.SalesPersonName [Customer''s Sales Person Name],
                   CASE WHEN V.PaymentMethod != ''Check'' THEN '''' WHEN V.OtherOwnerCheck=1 THEN ''No'' ELSE ''Yes'' END [Owner Check],
                         V.NoPPh [No. Bukti Potong PPh], V.DeductedPPh [Pot. PPh], V.TglPPh [Tanggal Bukti Potong PPh],
                   CASE WHEN IncomeOrExpense = 1 THEN dbo.UDF_FI_ARAP_CreateDescriptionForIncomeOrExpense(V.VoucherID) ELSE dbo.UDF_FI_ARAP_GetDescription(V.VoucherID) END Details,
                   CA.CustomerAreaCode [Customer Area Code], CA.CustomerAreaName [Customer Area Name], CSA.CustomerSubAreaCode [Customer Sub-Area Code], CSA.CustomerSubAreaName [Customer Sub-Area Name],
                   ISNULL(MS.SReturn, 0) [Sales Return], ISNULL(MS.SInvoice, 0) [Sales Invoice], ISNULL(MS.VRC,0) [Void Check],
                   ISNULL(MS.RD, 0) [AR Discount Note], ISNULL(MS.RN, 0) [AR Note],
                   ISNULL(MS.RDClosing, 0) [Over Receipt],
                   CASE WHEN V.OtherOwnerCheck = 1 THEN ''Other'' ELSE ''Own'' END [Check Owner],
                   BG.BusinessGroupName [Business Group Code], BG.Description [Business Group Name], V.CreatedBy [Created By], V.CreateDate [Create Date], CF.Description [Cash Flow Code], V.NetAmountPaid [Total Net Amount Paid],
                   ISNULL(LV.LevelName, '''') [EDU Level], CN.ClassName [EDU Class Name], M.MajorName [EDU Major],
                     dbo.UDF_FI_ARAP_GetInvoiceDetails (S.VoucherID) InvoiceDetails,
                     V.BankCharges [Bank Charges], PL.PackingListNo [Packing List No.],
                     V.EDUReportDate [EDU Report Date]
              FROM #Summary S
              LEFT JOIN (
            SELECT VI.VoucherID,
                   SUM(CASE WHEN VI.ItemType = ''SI'' THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) SInvoice,
                   SUM(CASE WHEN VI.ItemType = ''SR'' THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) SReturn,
                   SUM(CASE WHEN VI.ItemType = ''RD'' THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) RD,
                   SUM(CASE WHEN VI.ItemType = ''RN'' THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) RN,
                   SUM(CASE WHEN VI.ItemType = ''VRC'' THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) VRC,
                   SUM(CASE WHEN VI.ItemType = ''RD'' AND VI.FromClosing = 1 THEN VI.AmountPaid+VI.AmountCredit-VI.AmountDebit ELSE 0 END) RDClosing
              FROM FI_ARAPItems VI
              JOIN #Summary S ON S.VoucherID = VI.VoucherID
             WHERE (VI.AllocationDate <= @PerDate OR @PerDate IS NULL)
             GROUP BY VI.VoucherID) MS
                ON MS.VoucherID = S.VoucherID
              LEFT JOIN AR_Customers CS ON CS.[CustomerCode] = S.[Customer Code]
              LEFT JOIN AR_CustomerAreas CA ON CA.CustomerAreaID = CS.CustomerAreaID
              LEFT JOIN AR_CustomerSubAreas CSA ON CSA.CustomerSubAreaID = CS.CustomerSubAreaID
              LEFT JOIN AR_BusinessGroups BG ON BG.BusinessGroupID = CS.BusinessGroupID
              LEFT JOIN AR_SalesPersons SP ON SP.SalesPersonID = CS.SalesPersonID
              LEFT JOIN FI_ARAP V ON V.VoucherID = S.VoucherID
                LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = V.ExpenseOrRevenueTypeID AND V.IsAR = 0
                LEFT JOIN FI_ExpenseTypes ET ON ET.ExpenseTypeID = V.ExpenseOrRevenueTypeID AND V.IsAR = 1
                LEFT JOIN EXP_PackingList PL ON PL.PackingListID = V.ExSourceID AND V.ExSource = ''EXP.PackingList''
              LEFT JOIN FI_CashFlow CF ON CF.ID = V.CashFlowID
      LEFT JOIN EDU_StatusChange SCX ON SCX.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (CS.CustomerID, V.VoucherDate)
      LEFT JOIN EDU_Level LV ON LV.LevelID = SCX.LevelID
      LEFT JOIN EDU_ClassName CN ON CN.ClassnameID = SCX.ClassID
      LEFT JOIN EDU_Majors M ON M.MajorID = SCX.MajorID'
        END

        EXEC SP_ExecuteSQL @SQL, N'@OverdueAfter INT, @CMU BIT, @PerDate DATETIME', @OverdueAfter, @CMU, @PerDate
    END ELSE
        SELECT S.*, M.MajorName Major, SG.GroupName [GROUP], ST.AcademicYear [YEAR],
                 CASE WHEN ST.Active != 1 OR ST.QuitDate IS NOT NULL THEN 'Inactive' WHEN GraduationDate IS NOT NULL THEN 'Graduate' ELSE 'Active' END [STATUS]
          FROM #Summary S
          JOIN AR_Customers CS ON CS.CustomerCode = S.[Customer Code]
          JOIN EDU_Students ST ON ST.CustomerID = CS.CustomerID
          LEFT JOIN EDU_Majors M ON M.MajorID = ST.MajorID
          LEFT JOIN EDU_CIS_StudentGroups SG ON SG.GroupID = ST.GroupID

        IF @Version LIKE 'EDU%' BEGIN
            SELECT DISTINCT CS.CustomerCode [Customer Code], M.MajorName, L.LevelName, L.LevelCode, CN.ClassName, CS.Phone, CS.BillingAddressLine1
                FROM #Summary S
                JOIN AR_Customers CS ON CS.CustomerCode = S.[Customer Code]
                LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (CS.CustomerID, S.[Voucher DATE])
                LEFT JOIN EDU_Level L ON L.LevelID = SC.LevelID
                LEFT JOIN EDU_Majors M ON M.MajorID = SC.MajorID
                LEFT JOIN EDU_ClassName CN ON CN.ClassNameID = SC.ClassID

            SELECT S.[Voucher No.], N.EDUPeriod, N.EDURevenueTypeID, VI.AmountDebit Fine, -VI.AmountCredit Disc, VI.AmountPaid+VI.EDURemain Paid, RT.DisplayIndex,
                   VI.EDURemain, VI.VoucherItemID, -V.BankCharges Surcharge, SC.LevelID, SC.MajorID,
                         BA.BankAccountCode, BA.BankName, BA.AccountNo, V.EDUHideDiscount, ISNULL(V.EDUDiscReason, '') EDUDiscReason, VI.CreditNoteDescription, VI.DebitNoteDescription,
                         CASE WHEN RT.RevenueTypeDescription LIKE '%DAILY%' THEN N.DATE ELSE NULL END DATE
              INTO #RawItems
                FROM (SELECT DISTINCT [Voucher No.] FROM #Summary) S
                JOIN FI_ARAP V ON V.VoucherNo = S.[Voucher No.]
                JOIN FI_ARAPItems VI ON VI.VoucherID = V.VoucherID
                LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = V.BankAccountID
                JOIN FI_ARAPNotes N ON N.ARAPNoteID = VI.ItemID AND VI.ItemType IN ('RD', 'RN')
                JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = N.EDURevenueTypeID
                LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (N.AssociateID, V.VoucherDate)
             --WHERE RT.IncludeInRevenue = 1
             UNION ALL
            SELECT S.[Voucher No.], YEAR(InvoiceDate)*100+MONTH(InvoiceDate), RT.RevenueTypeID, VI.AmountDebit Fine, -VI.AmountCredit Disc, VI.AmountPaid+VI.EDURemain Paid, RT.DisplayIndex,
                   VI.EDURemain, VI.VoucherItemID, -V.BankCharges Surcharge, SC.LevelID, SC.MajorID,
                         BA.BankAccountCode, BA.BankName, BA.AccountNo, V.EDUHideDiscount, ISNULL(V.EDUDiscReason, '') EDUDiscReason, VI.CreditNoteDescription, VI.DebitNoteDescription, N.InvoiceDate
                FROM (SELECT DISTINCT [Voucher No.] FROM #Summary) S
                JOIN FI_ARAP V ON V.VoucherNo = S.[Voucher No.]
                LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = V.BankAccountID
                JOIN FI_ARAPItems VI ON VI.VoucherID = V.VoucherID
                JOIN AR_Invoices N ON N.InvoiceID = VI.ItemID AND VI.ItemType IN ('SI', 'SR')
                JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = dbo.UDF_AR_Invoices_GetEDURevenueTypeID (N.InvoiceID)
                LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (N.CustomerID, V.VoucherDate)
             --WHERE RT.IncludeInRevenue = 1

            SELECT [Voucher No.], EDURevenueTypeID, CAST('' AS VARCHAR(4000)) Periods, SUM(Paid-Fine-Disc) Paid, SUM(Disc) Disc, SUM(Fine) Fine,
                   CAST(0 AS INT) Period1, CAST(0 AS INT) Period2, DisplayIndex, SUM(EDURemain) Remaining, LevelID, MajorID, BankName,
                       BankAccountCode, AccountNo, ISNULL(EDUHideDiscount, 0) EDUHideDiscount, ISNULL(EDUDiscReason, '') EDUDiscReason
              INTO #Summ1
                FROM #RawItems
             GROUP BY [Voucher No.], EDURevenueTypeID, DisplayIndex, LevelID, MajorID, BankName, BankAccountCode, AccountNo, EDUHideDiscount, ISNULL(EDUDiscReason, '')

            DECLARE @VNo VARCHAR(50), @Period INT, @RTID INT
            DECLARE XCursor CURSOR LOCAL FOR
            SELECT [Voucher No.], EDURevenueTypeID, EDUPeriod, DATE
                FROM #RawItems RI
             ORDER BY 1, 2, 3

            OPEN XCursor
            DECLARE @XDate DATETIME
            FETCH FROM XCursor INTO @VNO, @RTID, @Period, @XDate
            WHILE @@FETCH_STATUS = 0 BEGIN
                IF @XDate IS NULL BEGIN
                    DECLARE @P1 INT, @P2 INT
                    SELECT @P1 = 0, @P2 = 0
                    SELECT @P1 = Period1, @P2 = Period2 FROM #Summ1 WHERE [Voucher No.] = @VNo AND EDURevenueTypeID = @RTID
                    IF @P1 = 0
                        UPDATE #Summ1 SET Period1 = @Period, Period2 = @Period, Periods = dbo.UDF_Common_Concat (Periods, dbo.UDF_Common_GetPeriodName (@Period)) WHERE EDURevenueTypeID = @RTID AND [Voucher No.] = @VNo
                    ELSE IF dbo.UDF_Common_AddMonthToPeriod (@P2, 1) = @Period
                        UPDATE #Summ1 SET Period2 = @Period, Periods = dbo.UDF_Common_Concat (Periods, dbo.UDF_Common_GetPeriodName (Period1) + ' to ' + dbo.UDF_Common_GetPeriodName (@Period)) WHERE EDURevenueTypeID = @RTID AND [Voucher No.] = @VNo
                    ELSE
                        UPDATE #Summ1 SET Period1 = @Period, Period2 = @Period, Periods = Periods + ', ' + dbo.UDF_Common_GetPeriodName (@Period) WHERE EDURevenueTypeID = @RTID AND [Voucher No.] = @VNo
                END ELSE BEGIN
                    UPDATE #Summ1 SET Periods = Periods + CASE WHEN LEN(Periods) > 0 THEN ', ' ELSE '' END + CONVERT(VARCHAR(30), @XDate, 106) WHERE EDURevenueTypeID = @RTID AND [Voucher No.] = @VNo
                END

                FETCH FROM XCursor INTO @VNO, @RTID, @Period, @XDate
            END
            DEALLOCATE XCursor

            SELECT V.VoucherNo, SUM(-V.BankCharges) Surcharge, SC.LevelID, SC.MajorID
              INTO #Surc
                FROM #Summary S
                JOIN FI_ARAP V ON V.VoucherNo = S.[Voucher No.]
                JOIN AR_Customers CS ON CS.CustomerID = V.AssociateID
                LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (CS.CustomerID, V.VoucherDate)
             WHERE -V.BankCharges != 0
             GROUP BY V.VoucherNo, LevelID, MajorID

            INSERT #Summ1 ([Voucher No.], EDURevenueTypeID, Periods, Paid, DisplayIndex, Disc, Fine, Remaining, LevelID, MajorID, EDUDiscReason, EDUHideDiscount)
            SELECT VoucherNo, 0, '', Surcharge, 9999, 0, 0, 0, LevelID, MajorID, '', 0
              FROM #Surc

            SELECT S.*, UPPER(COALESCE(RT.RevenueTypeDescription, 'SURCHARGE')) RevenueTypeDescription,
                   DX.Details Description, V.EDUHideDiscount, ISNULL(V.EDUDiscReason, '') EDUDiscReason,
                         CASE WHEN V.PaymentMethod = 'Check' THEN BA.BankName ELSE '' END BankName, BA.BankAccountCode, BA.AccountNo, BA.AccountOwner,
                         dbo.UDF_FI_ARAP_GetDescriptionEDU (V.VoucherID) DetailRemarks
              FROM #Summ1 S
                LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = S.EDURevenueTypeID
                JOIN FI_ARAP V ON V.VoucherNo= S.[Voucher No.]
                LEFT JOIN FI_ARAPDescription DX ON DX.VoucherID = V.VoucherID
                LEFT JOIN FI_BankAccounts BA ON BA.BankAccountID = V.BankAccountID
             ORDER BY [Voucher No.], COALESCE(RT.[DisplayIndex], S.DisplayIndex),
                   DX.Details, V.EDUHideDiscount, ISNULL(V.EDUDiscReason, '')

            SELECT L.LevelCode, L.LevelName, S.EDURevenueTypeID, SUM(Fine) Fine,
                   SUM(Disc) Disc, SUM(S.Paid) Paid, COALESCE(RT.DisplayIndex, S.DisplayIndex) DisplayIndex,
                         UPPER(COALESCE(RT.RevenueTypeDescription, 'SURCHARGE')) RevenueTypeDescription, SUM(S.Remaining) Remaining
                FROM #Summ1 S
                LEFT JOIN EDU_Level L ON L.LevelID = S.LevelID
                LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = S.EDURevenueTypeID
             GROUP BY L.LevelCode, L.LevelName, COALESCE(RT.DisplayIndex, S.DisplayIndex), S.EDURevenueTypeID, RT.RevenueTypeDescription

            SELECT L.MajorName, S.EDURevenueTypeID, SUM(Fine) Fine,
                   SUM(Disc) Disc, SUM(S.Paid) Paid, COALESCE(RT.DisplayIndex, 9999) DisplayIndex, UPPER(COALESCE(RT.RevenueTypeDescription, 'SURCHARGE')) RevenueTypeDescription, SUM(S.Remaining) Remaining
                FROM #Summ1 S
                LEFT JOIN EDU_Majors L ON L.MajorID = S.MajorID
                LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = S.EDURevenueTypeID
             GROUP BY L.MajorName, COALESCE(RT.DisplayIndex, 9999), RT.RevenueTypeDescription, S.EDURevenueTypeID

            SELECT S.EDURevenueTypeID, SUM(Fine) Fine,
                   SUM(Disc) Disc, SUM(S.Paid) Paid, COALESCE(RT.DisplayIndex, 9999) DisplayIndex, UPPER(COALESCE(RT.RevenueTypeDescription, 'SURCHARGE')) RevenueTypeDescription, SUM(S.Remaining) Remaining
                FROM #Summ1 S
                LEFT JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = S.EDURevenueTypeID
             GROUP BY COALESCE(RT.DisplayIndex, 9999), RT.RevenueTypeDescription, S.EDURevenueTypeID
/*
            DECLARE @Src MONEY
            SELECT @Src = SUM(-V.BankCharges)
                FROM #Summary S
                JOIN FI_ARAP V ON V.VoucherNo = S.[Voucher No.]
            SET @Src = ISNULL(@Src, 0)

            SELECT N.EDURevenueTypeID, SUM(VI.AmountDebit) Fine,
                   SUM(VI.AmountCredit) Disc, SUM(VI.AmountPaid) Paid, RT.DisplayIndex, RT.RevenueTypeDescription
                FROM #Summary S
                JOIN FI_ARAP V ON V.VoucherNo = S.[Voucher No.]
                JOIN FI_ARAPItems VI ON VI.VoucherID = V.VoucherID
                JOIN FI_ARAPNotes N ON N.ARAPNoteID = VI.ItemID AND VI.ItemType IN ('RD', 'RN')
                JOIN FI_RevenueTypes RT ON RT.RevenueTypeID = N.EDURevenueTypeID
                JOIN AR_Customers CS ON CS.CustomerCode = S.[Customer Code]
                LEFT JOIN EDU_StatusChange SC ON SC.StatusID = dbo.UDF_AR_Customers_GetStatusChangeRecordID (CS.CustomerID, S.[Voucher Date])
                LEFT JOIN EDU_Level L ON L.LevelID = SC.LevelID
                LEFT JOIN EDU_Majors M ON M.MajorID = SC.MajorID
                LEFT JOIN EDU_ClassName CN ON CN.ClassNameID = SC.ClassID
             GROUP BY N.EDURevenueTypeID, RT.DisplayIndex, RT.RevenueTypeDescription
             UNION ALL
            SELECT 0, 0, 0, @Src, 9999, 'SURCHARGE' WHERE @Src != 0*/
        END
END ELSE IF @ReportType = 'AdvanceClosing' BEGIN
    SELECT V.VoucherDate [Voucher DATE], V.VoucherNo [Voucher No.], VI.AllocationDate [Closing DATE], VI.AmountPaid [Amount Closed],
           N.NUMBER [Closing NUMBER], VI.Remarks, CS.CustomerCode [Customer Code], CS.CustomerName [Customer Name],
           CA.CustomerAreaCode [Customer Area Code], CA.CustomerAreaName [Customer Area Name],
           CSA.CustomerSubAreaCode [Customer Sub-Area Code], CSA.CustomerSubAreaName [Customer Sub-Area Name],
           CT.CustomerTypeName [Customer TYPE],
           CH.CustomerChannelCode [Customer Channel Code], CH.CustomerChannelName [Customer Channel Name],
           CSC.CustomerScaleCode [Customer Scale Code], CSC.CustomerScaleName [Customer Scale Name], CU.Currency, V.ExchangeRate [Exchange Rate],
           CASE WHEN N.ForceClosing = 1 THEN 'Force' ELSE 'Used' END [Closing Method]
      FROM FI_ARAPItems VI
      JOIN FI_ARAP V ON V.VoucherID = VI.VoucherID
      LEFT JOIN dbo.UDF_Common_Split (@VoucherNumbers, ',') VN ON V.VoucherNo LIKE VN.VALUE
      LEFT JOIN FI_ARAPNotes N ON N.ARAPNoteID = VI.ItemID
      JOIN AR_Customers CS ON CS.CustomerID = V.AssociateID
     JOIN #SelectedMembers SM ON SM.MemberID = V.AssociateID
      JOIN CO_Currency CU ON CU.CurrencyID = V.CurrencyID
      LEFT JOIN AR_CustomerAreas CA ON CA.CustomerAreaID = CS.CustomerAreaID
      LEFT JOIN AR_CustomerSubAreas CSA ON CSA.CustomerSubAreaID = CS.CustomerSubAreaID
      LEFT JOIN AR_CustomerTypes CT ON CT.CustomerTypeID = CS.CustomerTypeID
      LEFT JOIN AR_CustomerChannel CH ON CH.CustomerChannelID = CS.CustomerChannelID
      LEFT JOIN AR_CustomerScale CSC ON CSC.CustomerScaleID = CS.CustomerScaleID
     WHERE AdvClosing = 1
       AND (AllocationDate BETWEEN @StartDate AND @EndDate OR @StartDate IS NULL OR @EndDate IS NULL)
       AND (V.Void = 0 OR V.VoidCheck = 1)
       AND (@VoucherNumbers IS NULL OR VN.VALUE IS NOT NULL)
       AND (@CurrencyID = 0 OR @CurrencyID = V.CurrencyID)
     ORDER BY 1, 2
END