Stored procedure for Draft

[former_member217682]

Hi all,

I have a condition here that would like to apply it to a stored procedure in B1.

Condition : -

If

drf.Quantity + pdn1.Quantity > por1.Quantity

ERROR

I've came up with a stored procedure but i can't figure out whats wrong.

IF @OBJECT_TYPE='20' AND @TRANSACTION_TYPE='A'

BEGIN

    DECLARE @draftqty INT

    DECLARE @quantity INT

    DECLARE @poqty INT

    SELECT @docentry = 0

    SELECT @docentry = CONVERT(INT, SUBSTRING(@list_of_cols_val_tab_del, 1, 10))

    SELECT @draftqty=quantity FROM drf1 WHERE drf1.docentry=@docentry and objtype =20   and

    SELECT @poqty=quantity FROM por1 where por1.docentry=@docentry

    SELECT @quantity=quantity FROM PDN1 WHERE PDN1.DOCENTRY=@DOCENTRY

    IF  @quantity + @draftqty > @poqty

    BEGIN

    SET @error = 50

    SET @error_message = 'You have exceed the open quantity!'

    END

END

how can i fix this query? and what Object type is ODRF table?

Thank you.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please refer this thread .

There is no need of draft quantity in this validation. Draft document will not increase stock quantity.

Thanks & Regards,

Nagarajan

[javier_facessantos · Accepted solution]

Hello Nagarajan

You are right, Draft document won't increase stock quantity.

But I think that Adrian wants to avoid Draft Document to be even added, if the quantity exceeds open quantity on PO, so PO's quantity could never be exceeded.

I have wrote following query to do that , but it is not working at my end as it does not avoid draft document addition:

IF @object_type = '112' AND @transaction_type IN(N'A',N'U')

BEGIN

      IF Exists (SELECT T0.DocEntry FROM [DRF1] T0 INNER JOIN [ODRF] T1 ON T0.[DocEntry] = T1.[DocEntry]  LEFT JOIN [OPOR] T2 ON T2.[DraftKey] = T0.[DocEntry] AND T0.[BaseType] = '22'  INNER JOIN [POR1] T3 ON T2.[DocEntry] = T3.[DocEntry] AND T3.[LineNum] = T0.[BaseLine]

WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T0.[ObjType] = '20'

      AND T0.[Quantity]> T3.[OpenQty])        

   BEGIN

            SELECT @error = 1

            SELECT @error_message = 'Mensaje sistema!: You have exceed the open quantity”'

      END

END

But this other query that I have wrote to avoid GR addition when GR quantity exceeds PO open quantity (considering that there is not draft or approval process when PO is exceeded) is working just fine:

IF @object_type = '20' AND @transaction_type IN(N'A',N'U')

BEGIN

      IF Exists (SELECT T0.DocEntry FROM [PDN1] T0 INNER JOIN [OPDN] T1 ON T0.[DocEntry] = T1.[DocEntry]  LEFT JOIN POR1 T2 ON T2.[DocEntry] = T0.[BaseEntry] AND T0.[BaseType] = '22' AND T2.[LineNum] = T0.[BaseLine]

WHERE T0.DocEntry = @list_of_cols_val_tab_del

      AND T0.[Quantity]> T2.[OpenQty] )        

   BEGIN

            SELECT @error = 1

            SELECT @error_message = 'Mensaje sistema!: You have exceed the open quantity”'

      END

END

I will keep working on the first query, to see if I am able to find the bug and make it work.

Regards

[former_member217682 · Accepted solution]

Hi Javier,

Thank you so much for your help.

I have the stored procedure to avoid GR addition when GR quantity exceeds PO open quantity (considering that there is not draft or approval process when PO is exceeded).

However, just like you said, i need a stored procedure to prevent overdraft.

I will try to work on the stored procedure provided by you.

Thank you.

[javier_facessantos]

Hello Adrian

Object type for draft documents is '112'.

But I am not really understanding the purpose of this validation.Could you explain it more in-depth?

Regards

[former_member217682]

Hi Javier,

I want to make sure that GRN is not submitting the quantity more than PO quantity.

For example : i have a PO with quantity 100 for item A

                     i have GRN today as well for 50 item A = Open quantity should have 50 left

                     the following day i have a GRN for item A but not finalise yet but quantity 60 =                      GRN will have to save the document in DRAFT.

                     so once the PO is confirmed, GRN are submit the draft for item A for 60 quantity

So if u see the scenario above, it's wrong. GRN is over-receiving

Initial PO quantity for item A is 100

But total GRN cut is 110.

I'm trying to write a stored procedure to prevent all this from happening.

Thank you.