Reserve Inventory for sales orders

[fbio_bilicki]

Hi all,

I'm looking to make a dynamic reservation on SQL Server but I'm looking to make a dynamic reservation on my query, could someone help me?

Below the code, it is duplicating lines when reading the order.

WITH
CTE_PEDIDOS as
(    
    SELECT 
    row_number() over(partition by Itemcode order by ShipDate, DocEntry, LineNum) as RN,
    DocEntry,
    ItemCode,
    CAST(Dscription AS VARCHAR(MAX))[Dscription],
    Quantity, 
    OpenQty,
    ShipDate
    FROM RDR1
    WHERE LineStatus = 'O'
),

CTE_ESTOQUE as
(
    select    
    C.DocEntry,
    C.ItemCode,
    C.Dscription,
    C.Quantity, 
    C.OpenQty,
    C.ShipDate,
    CAST(case when I.OnHand > C.OpenQty then C.OpenQty else I.OnHand end AS NUMERIC(19,6))[Reserva],
    CAST(case when I.OnHand > C.OpenQty then I.OnHand - C.OpenQty else 0 end AS NUMERIC(19,6))[Restante],
    I.OnHand,
    C.RN
    from CTE_PEDIDOS as C
    left join (
            SELECT ItemCode,OnHand FROM OITM
            ) I on I.Itemcode = C.ItemCode
    where
        C.RN = 1
            
    union all
        
    select    
    C.DocEntry,
    C.ItemCode,
    C.Dscription,
    C.Quantity, 
    C.OpenQty,
    C.ShipDate,
    CAST(case when R.Restante > C.OpenQty then C.OpenQty else R.Restante end AS NUMERIC(19,6)),
    CAST(case when R.Restante > C.OpenQty then R.Restante - C.OpenQty else 0 end AS NUMERIC(19,6)),
    R.OnHand,
    (C.RN + 1)[RN]
      from CTE_ESTOQUE as R
    inner join CTE_PEDIDOS C on C.[ItemCode] = R.ItemCode and C.RN = R.RN
)

SELECT * FROM CTE_ESTOQUE 
ORDER BY ShipDate, DocEntry, RN

Best Regards,

Fabio

SAP Business One 10.0 PL02

SQL Server 2017

[kothandaraman_nagarajan]

Hi,

The problem with below part, check this part,

unionallselectC.DocEntry,C.ItemCode,C.Dscription,C.Quantity,C.OpenQty,C.ShipDate,CAST(casewhen R.Restante >C.OpenQty thenC.OpenQty else R.Restante endAS NUMERIC(19,6)),CAST(casewhen R.Restante >C.OpenQty then R.Restante -C.OpenQty else0endAS NUMERIC(19,6)),
    R.OnHand,(C.RN +1)[RN]from CTE_ESTOQUE as R
    innerjoin CTE_PEDIDOS ConC.[ItemCode]= R.ItemCode andC.RN = R.RN

[fbio_bilicki]

I know, but I can't solve it, if I have the solution I appreciate it.