ALTER PROCEDURE dbo.BUDGET_COMMITMENT_QTY_REPORT
(
@BudgetYear NVARCHAR(50) = null,
@BudgetVersion NVARCHAR(50) = null,
@Company NVARCHAR(50) = null,
@Division NVARCHAR(50) = null,
@CostCenter NVARCHAR(50) = null,
@SubAccount NVARCHAR(50) = null,
@SalesDivision NVARCHAR(50) = null,
@AsOnDate DATETIME = null
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @Select NVARCHAR(4000)
DECLARE @Param NVARCHAR(4000)
SET @Param = ' @BudgetYear NVARCHAR(50) = null,
@BudgetVersion NVARCHAR(50) = null,
@Company NVARCHAR(50) = null,
@Division NVARCHAR(50) = null,
@CostCenter NVARCHAR(50) = null,
@SubAccount NVARCHAR(50) = null,
@SalesDivision NVARCHAR(50) = null,
@AsOnDate DATETIME = null '
CREATE TABLE #TEMPTABLE
(
BudgetType NVARCHAR(50), BudgetYear NVARCHAR(50), Company NVARCHAR(50), Division NVARCHAR(50), SalesDivision NVARCHAR(50), Costcenter NVARCHAR(50), SubAccount NVARCHAR(50), Display NVARCHAR(50), Amount DECIMAL(29,2), Quantity DECIMAL(29,2), RQDate DateTime
)
INSERT INTO #TEMPTABLE
SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS year, COMPANY_CODE AS Company, DIVISION_CODE AS Division,
SALES_DIVISION AS SalesDivision, COST_CENTER AS costCenter, SUB_ACCOUNT_CODE AS subAccount, 'Precommitment' AS Display, SUM(AMOUNT)
AS TotalValue, SUM(QUANTITY) AS Quantity, PR_REQ_DATE AS RQDate
FROM ECMS_PR_BUDGET_CHECK
GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, QUANTITY, DIVISION_CODE,
PR_REQ_DATE
UNION ALL
SELECT BUDGET_TYPE AS BudgetType, BUDGET_YEAR AS BudgetYear, COMPANY_CODE AS Company, DIVISION_CODE AS Division,
SALES_DIVISION AS SalesDivision, COST_CENTER AS CostCenter, SUB_ACCOUNT_CODE AS SubAccount, 'Commitment' AS Display, SUM(AMOUNT)
AS TotalValue, SUM(QUANTITY) AS Quantity, PO_REQ_DATE AS RQDate
FROM ECMS_PO_BUDGET_CHECK
GROUP BY BUDGET_TYPE, BUDGET_YEAR, COMPANY_CODE, SALES_DIVISION, COST_CENTER, SUB_ACCOUNT_CODE, DIVISION_CODE, PO_REQ_DATE
--SELECT * FROM #TEMPTABLE
SET @Select = 'SELECT #TEMPTABLE.BudgetYear, #TEMPTABLE.Display, #TEMPTABLE.Amount,
#TEMPTABLE.Quantity, ECMS_MST_COMPANY.Company_Name, ECMS_MST_COST_CENTER.Cost_center_desc,
ECMS_MST_SUB_ACCOUNT.SUB_AC_NAME, ECMS_MST_SALES_DIVISION.SALES_DIVISION_NAME , ECMS_MST_DIVISION.DIVISION_NAME
FROM #TEMPTABLE
INNER JOIN
ECMS_MST_BUDGET_TYPE ON #TEMPTABLE.BudgetType = ECMS_MST_BUDGET_TYPE.BUDGET_CODE INNER JOIN
ECMS_MST_COMPANY ON #TEMPTABLE.Company = ECMS_MST_COMPANY.Company_Code INNER JOIN
ECMS_MST_COST_CENTER ON #TEMPTABLE.Costcenter = ECMS_MST_COST_CENTER.Cost_center_code INNER JOIN
ECMS_MST_SUB_ACCOUNT ON #TEMPTABLE.SubAccount = ECMS_MST_SUB_ACCOUNT.SUB_AC_CODE INNER JOIN
ECMS_MST_SALES_DIVISION ON #TEMPTABLE.SalesDivision = ECMS_MST_SALES_DIVISION.SALES_DIVISION_CODE INNER JOIN
ECMS_MST_DIVISION ON #TEMPTABLE.Division = ECMS_MST_DIVISION.DIVISION_CODE '
IF @BudgetYear<>0 AND @BudgetYear IS NOT NULL
SET @Select = @Select+ ' AND (BudgetYear=@BudgetYear) '
IF @BudgetYear IS NOT NULL
SET @Select = @Select+ ' AND (BudgetType=@BudgetVersion) '
IF @Company<>0 AND @Company IS NOT NULL
SET @Select = @Select+ ' AND (Company=@Company) '
IF @Division<>0 AND @Division IS NOT NULL
SET @Select = @Select+ ' AND (Division=@Division) '
IF @SalesDivision<>0 AND @SalesDivision IS NOT NULL
SET @Select = @Select+ ' AND (SalesDivision=@SalesDivision) '
IF @Costcenter<>0 AND @Costcenter IS NOT NULL
SET @Select = @Select+ ' AND (Costcenter=@Costcenter) '
IF @SubAccount<>0 AND @SubAccount IS NOT NULL
SET @Select = @Select+ ' AND (SubAccount=@SubAccount) '
IF @AsOnDate<>0 AND @AsOnDate IS NOT NULL
SET @Select = @Select+ ' AND (RQDate<=@AsOnDate) '
--print @Select
Execute sp_Executesql @Select, @Param , @BudgetYear, @BudgetVersion, @Company, @Division, @CostCenter, @SubAccount, @SalesDivision, @AsOnDate
END
SET NOCOUNT OFF
RETURN
