Skip to Content
avatar image
Former Member

Need help for SP

Dear Team,

Below i mentioned my stored procedure for Block production order if there is no stock.

Actually t0.U_stockcbe  is a FMS alphanumeric field contains stock in warehouse.But this sp is not working correctly.

please help

IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'202')

if  Exists( SELECT   T0.[LineNum]  FROM WOR1 T0  INNER JOIN OWOR T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[PlannedQty]>t0.U_stockcbe 

and T1.DocEntry= @list_of_cols_val_tab_del )

  Begin

  Set @error =9

  Set @error_message = 'prd Orderd Quantity is greater than Stock!! '

  End

End

Regards

Sanu

PR.png (24.8 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 01, 2015 at 09:43 AM

    Hi Sanu,

    below SP checks the Stock in selected warehouse no need of UDF

    -- Prd. Order Avaiable Qty

    IF @transaction_type IN ('A','U') AND @Object_type = '202'

    BEGIN

    If exists ( SELECT     T0.DocNum, T1.BaseQty, T1.PlannedQty, T2.OnHand, T1.ItemCode, T2.OnHand - T1.PlannedQty AS Avail

    FROM         OWOR AS T0 INNER JOIN WOR1 AS T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

    OITW AS T2 ON T1.ItemCode = T2.ItemCode AND T1.wareHouse = T2.WhsCode

    --INNER JOIN OITM AS T3 ON T1.ItemCode = T3.ItemCode

    WHERE     (T2.OnHand - T1.PlannedQty < 0) AND T0.[DocEntry] = @list_of_cols_val_tab_del

    and (select itemtype from OITM where ItemCode = T1.ItemCode) <> 'L' )

    BEGIN

    SELECT @Error = 15, @error_message = 'Qty. not Available in the selected WHS'

    End

    end

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 02, 2015 at 01:15 AM

    Hi,

    Try:

    IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'202')

    if  Exists( SELECT   T1.Docentry  FROM WOR1 T0  INNER JOIN OWOR T1 ON T0.DocEntry = T1.DocEntry

    WHERE T0.[PlannedQty] > t0.U_stockcbe 

    and T1.DocEntry= @list_of_cols_val_tab_del )

      Begin

      Set @error =9

      Set @error_message = 'prd Orderd Quantity is greater than Stock!! '

      End

    End

    Also refer attached thread without using UDF.

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 01, 2015 at 09:50 AM

    your query

    IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'202')

    if  Exists( SELECT   T0.[LineNum]  FROM WOR1 T0  INNER JOIN OWOR T1 ON T0.DocEntry = T1.DocEntry

    WHERE T0.[PlannedQty]>=t0.U_stockcbe

    and T1.DocEntry= @list_of_cols_val_tab_del )

      Begin

      Set @error =9

      Set @error_message = 'prd Orderd Quantity is greater than Stock!! '

      End

    End

    do also check if there is any labor item in your Prd Order neglect those item

    Add comment
    10|10000 characters needed characters exceeded