USE [FinanceReporting]
    GO

SELECT
    [Id],
    [Business Unit],
    [Source],
    [Category],
    [Journal Batch],
    [Journal Name],
    [Journal Description],
    [Journal Line Num],
    [Journal Line Description],
    [Journal Date],
    [Entered Amount],
    [Entered Currency],
    [Accounted Amount],
    [Accounted Currency],
    [Account],
    [Account Name],
    [DEPT],
    [LOCATION],
    [DEST],
    [Reference Code],
    [AFE],
    [Affiliate],
    [MX WO Number],
    [Invoice Num],
    [Invoice Description],
    [Invoice Line Description],
    [Invoice Date],
    [Purchase order],
    [Maximo PO#],
    [Maximo WO],
    [Vendor Number],
    [Vendor Name],
    [AP Trasaction amount],
    [AP Transaction currency],
    [AP Base Amount],
    [AP Base currency],
    [Tax Code],
    [Tax Rate],
    [Tax Amt.]
FROM   [FinanceReporting].[dbo].[RPT]
UNION
SELECT
    /* [Id]*/ 1 Id,
              'MHT01' AS [Business Unit],
    'Budget' AS [Source],
    'Budget' AS [Category],
    'Budget' AS [Journal Batch],
    'Budget' AS [Journal Name],
    'Budget' AS [Journal Description],
    0 AS [Journal Line Num],
    'Budget' AS [Journal Line Description],
    CONCAT([Year], '-',
    [Month], '-01') AS [Journal Date],
    0 AS [Entered Amount],
    'CAD' AS [Entered Currency],
    0 AS [Accounted Amount],
    'CAD' AS [Accounted Currency], [Account],
    [Account Name] AS [Account Name],
    [Cost Center] AS [DEPT],
    121 AS [LOCATION],
    0 AS [DEST],
    'Budget' AS [Reference Code],
    'Budget' AS [AFE],
    'Budget' AS [Affiliate],
    'Budget' AS [MX WO Number],
    'Budget' AS [Invoice Num],
    'Budget' AS [Invoice Description],
    'Budget' AS [Invoice Line Description],
    '2022-01-01' AS [Invoice Date],
    'Budget' AS [Purchase order],
    0 AS [Maximo PO#],
    'Budget' AS [Maximo WO],
    0 AS [Vendor Number],
    'Budget' AS [Vendor Name],
    0 AS [AP Trasaction amount],
    'CAD' AS [AP Transaction currency]
    /* ,[Monthly Budget] as [AP Base Amount]*/ ,
    FinanceReporting.dbo.getMonthlyBudget([Cost Center],
    [Account],
    YEAR(getDate())) AS [AP Base Amount],
    'CAD' AS [AP Base currency],
    'Budget' AS [Tax Code],
    'Budget' AS [Tax Rate],
    'Budget' AS [Tax Amt.]
FROM [FinanceReporting].[dbo].[MonthlyBudget]
WHERE [Month] > (SELECT MONTH(MAX([Journal Date])) FROM  RPT)
UNION
SELECT
    /* [Id]*/ 1 Id,
              'MHT01' AS [Business Unit],
    'Forecast' AS [Source],
    'Forecast' AS [Category],
    'Forecast' AS [Journal Batch],
    'Forecast' AS [Journal Name],
    'Forecast' AS [Journal Description],
    0 AS [Journal Line Num],
    'Forecast' AS [Journal Line Description],
    CONCAT([Year], '-',
    [Month], '-01') AS [Journal Date],
    0 AS [Entered Amount],
    'CAD' AS [Entered Currency],
    0 AS [Accounted Amount],
    'CAD' AS [Accounted Currency], [Account],
    [Account Name] AS [Account Name],
    [Department] AS [DEPT],
    121 AS [LOCATION],
    0 AS [DEST],
    'Forecast' AS [Reference Code],
    'Forecast' AS [AFE],
    'Forecast' AS [Affiliate],
    'Forecast' AS [MX WO Number],
    'Forecast' AS [Invoice Num],
    'Forecast' AS [Invoice Description],
    'Forecast' AS [Invoice Line Description],
    '2022-01-01' AS [Invoice Date],
    'Forecast' AS [Purchase order],
    0 AS [Maximo PO#],
    'Forecast' AS [Maximo WO],
    0 AS [Vendor Number],
    'Forecast' AS [Vendor Name],
    0 AS [AP Trasaction amount],
    'CAD' AS [AP Transaction currency]
    /* ,[Monthly Budget] as [AP Base Amount]*/ ,
    FinanceReporting.dbo.getMonthlyBudget([Department],
    [Account],
    YEAR(getDate())) AS [AP Base Amount],
    'CAD' AS [AP Base currency],
    'Forecast' AS [Tax Code],
    'Forecast' AS [Tax Rate],
    'Forecast' AS [Tax Amt.]
FROM [FinanceReporting].[dbo].[Forecast]
WHERE [Month] > (SELECT MONTH(MAX([Journal Date])) FROM  RPT)
