DECLARE @Rng_CashRequirement int = 0 --0 = All, 1 = Range, 2 = Individual DECLARE @Rng_CashRequirement_Start nvarchar(30) = '' DECLARE @Rng_CashRequirement_End nvarchar(30) = '' DECLARE @Rng_CashRequirement_Individual nvarchar(MAX) = '' DECLARE @Rng_CashRequirement_Field int = 0 --0 = Vendor Identifier, 1 = Part Identifier, 2 = Part Identifier/Due Date, 3 = Order Number DECLARE @IncludeInventory bit = 1 DECLARE @IncludeNonInventory bit = 1 DECLARE @IncludeAllDates bit = 1 DECLARE @StartDate date = NULL DECLARE @EndDate date = NULL DECLARE @BuyerStart nvarchar(30) = '' DECLARE @BuyerEnd nvarchar(30) = '' DECLARE @AddVendorTermsToDueDate bit = 1 SET @Rng_CashRequirement = 0 --0 = All, 1 = Range, 2 = Individual SET @Rng_CashRequirement_Start = '' -- Start Part Number SET @Rng_CashRequirement_End = '' -- Start End Number SET @Rng_CashRequirement_Individual = '' SET @Rng_CashRequirement_Field = 0 --0 = Vendor Identifier, 1 = Part Identifier, 2 = Part Identifier/Due Date, 3 = Order Number SET @IncludeInventory = 0 SET @IncludeNonInventory = 0 SET @IncludeAllDates = 0 SET @StartDate = '' SET @EndDate = '' SET @BuyerStart = '' SET @BuyerEnd = '' SET @AddVendorTermsToDueDate = 0 IF @Rng_CashRequirement = 2 AND (@Rng_CashRequirement_Individual IS NULL OR LEN(RTRIM(@Rng_CashRequirement_Individual)) = 0) BEGIN RETURN END DECLARE @Select nvarchar(MAX) DECLARE @Where nvarchar(MAX) DECLARE @OrderBy nvarchar(MAX) DECLARE @Temp nvarchar(MAX) DECLARE @WhereField nvarchar(MAX) -- Conversion >> RTRIM(DESC_10) AS DESC_10, SET @Select = 'SELECT 2 AS QuantityDecimals, 2 AS PriceDecimals, dbo.func_MAX_GetPOTermsBucket(PRTNUM_10, CURDUE_10, TERMS_16, ' + CAST(@AddVendorTermsToDueDate AS nchar(1)) + ') AS Bucket, ORDER_10, TERMS_16, CURDUE_10, RTRIM(PRTNUM_10) AS PRTNUM_10, RTRIM(VENID_10) AS VENID_10, ORDNUM_10, LINNUM_10, DELNUM_10, FRMPLN_10, CURQTY_10, DUEQTY_10, CURPRM_10, RTRIM(BUYER_10) AS BUYER_10, RTRIM(PLANID_10) AS PLANID_10, STATUS_10, TYPE_10, RTRIM(PMDES1_01) AS PMDES1_01, RTRIM(PURUOM_01) AS PURUOM_01, RTRIM(PURUOM_10) AS PURUOM_10, PURCNV_01, PURSTK_01, STATUS_08, EXCRTE_16, COST_01, COST_10, FORCUR_10, 1 AS ExchangeRate, dbo.func_MAX_CalculatePODueDateFromMRPDueDate(CURDUE_10, PRTNUM_10) AS PODueDate, dbo.func_MAX_AddTerms(dbo.func_MAX_CalculatePODueDateFromMRPDueDate(CURDUE_10, PRTNUM_10), TERMS_16, ''P'') AS VendorTermsAdded FROM Order_Master LEFT JOIN Vendor_Master ON VENID_10 = VENID_08 LEFT JOIN Part_Master ON PRTNUM_01 = PRTNUM_10 LEFT JOIN Purchase_Order_Code ON ORDNUM_16 = ORDNUM_10' SET @Where = ' WHERE ORDNUM_10 >= ''70000000'' AND ORDNUM_10 <= ''79999999'' AND STATUS_10 = ''3''' IF @IncludeInventory <> @IncludeNonInventory BEGIN IF @IncludeInventory = 1 SET @Where = @Where + ' AND TYPE_10 <> ''NI''' ELSE IF @IncludeNonInventory = 1 SET @Where = @Where + ' AND TYPE_10 = ''NI''' END SET @Temp = dbo.func_MAX_rptWhereRange(1, 'BUYER_10', @BuyerStart, @BuyerEnd, null) IF @Temp IS NOT NULL AND LEN(RTRIM(@Temp)) > 0 SET @Where = @Where + ' AND ' + @Temp IF @IncludeInventory = 1 AND @Rng_CashRequirement_Field = 2 AND @IncludeAllDates = 0 BEGIN SET @Temp = dbo.func_MAX_rptWhereRange(1, 'CURDUE_10', @StartDate, @EndDate, null) IF @Temp IS NOT NULL AND LEN(RTRIM(@Temp)) > 0 SET @Where = @Where + ' AND ' + @Temp END SET @WhereField = CASE WHEN @Rng_CashRequirement_Field = 1 OR @Rng_CashRequirement_Field = 2 THEN 'PRTNUM_10' WHEN @Rng_CashRequirement_Field = 3 THEN 'ORDNUM_10' ELSE 'VENID_10' END SET @Temp = dbo.func_MAX_rptWhereRange(@Rng_CashRequirement, @WhereField, @Rng_CashRequirement_Start, @Rng_CashRequirement_End, @Rng_CashRequirement_Individual) IF @Temp IS NOT NULL AND LEN(RTRIM(@Temp)) > 0 SET @Where = @Where + ' AND ' + @Temp SET @OrderBy = ' ORDER BY ' + @WhereField EXEC (@Select + @Where + @OrderBy)