Download and rename to .sql and open in Management Studio — Create a Stored Procedure using this code
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)