production order store procedure

[Former Member]

Hi,

After creating Production order , our user making issue from production and receipt from production . here they do some mistake it is means

Some time they make receipt more than the planned qty (Planned qty < Completed qty)

or

some time they make receipt correct way .but they do mistake on issue qty . they make more issue qty then the planned qty or low issue qty

after that they closed the work order

So we need to block close the work order status where they make more receipt or more issue or low issue or Low receipt

is it possible? it is possible give provide sp.

[Former Member · Accepted solution]

Hi Ramesh,

Give this one a try:

IF @object_type = '202' AND @transaction_type= 'U'

    BEGIN

        IF EXISTS

            (

                SELECT T1.DocEntry

                FROM OWOR T0

                INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

                WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T0.Status = 'L' AND T0.CmpltQty <> T0.PlannedQty

            )

        BEGIN

            SET @error = 500

            SET @error_message = 'Completed quantity does not equal planned quantity (Header). Please rectify before closing.'

        END

    END

IF @object_type = '202' AND @transaction_type= 'U'

    BEGIN

        IF EXISTS

            (

                SELECT T1.DocEntry

                FROM OWOR T0

                INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

                WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T0.Status = 'L' AND T1.IssuedQty <> T1.PlannedQty

            )

        BEGIN

            SET @error = 500

            SET @error_message = 'Issued quantity does not equal planned quantity (Lines). Please rectify before closing.'

        END

    END

It is comparing the header completed quantity vs planned quantity, and also the lines issued quantity vs planned quantity.

Kind Regards,

Nick Lakasas

[former_member269992]

Hi Ramesh,

Please try below query in store procedure.

IF @transaction_type='A' AND CAST(@object_type AS NVARCHAR) = '310000003'

BEGIN

    IF EXISTS(SELECT PlannedQty - CmpltQty AS BalQty FROM dbo.OWOR AS T0 WHERE (PlannedQty - CmpltQty < 0))

    BEGIN

        SET @error = 1401 --151618192021596067162

        SET @error_message = 'Completed Quantity cannot be more than Planned Quantity... Sorry...'

    END

END

Regards,

Hitul