Stop Release of Production Order

[former_member252592]

Hi Experts I want to Stop Release the Production Order if certian components are not in Production Order for that i write a SP

-/*Block the update of Production Order if Inks & Solvents not Present */

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

BEGIN

IF EXISTS (SELECT T0.DocEntry FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

Left Join OITM T2 on T2.ItemCode = T1.ItemCode

WHERE T0.ItemCode Like 'SF-P%%' and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and (T2.Series not in ('101') or T2.Series not in ('53')

or T2.Series not in ('164')or T2.Series not in ('103')or T2.Series not in ('55')))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

END

the above SP is not working as per my requirement it is blocking its release either those items are present or not

Can any one tell me what am i missing in this sp?

Kind Regards,

Jamil

[former_member252592 · Accepted solution]

Thank you all guyz to  help me out after all efforts finally my issue is resolved by the following SP

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

BEGIN

IF Exists (SELECT T0.DocEntry

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

Where T0.ItemCode Like 'SF-P%%' and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del)

  BEGIN

   IF (SELECT Count(A.ItemCode)

    From WOR1 A inner join OITM B on A.ItemCode = B.ItemCode

    Where A.DocEntry = @list_of_cols_val_tab_del and B.Series in ('101','53','164','103','55')) < 1

   BEGIN

    SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

   END

  END

END

Thanx & Regards

Jamil

[kothandaraman_nagarajan]

Hi,

Try this:

IF EXISTS (SELECT T0.[DocNum]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[ItemCode] NOT  IN (select t0.itemcode from OITM T0 where T0.itemcode = 'SF-P')and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and (T2.Series not in ('101') or T2.Series not in ('53')

or T2.Series not in ('164')or T2.Series not in ('103')or T2.Series not in ('55')))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

END

Thanks

[former_member252592]

Thanx for your reply Nagarajan K its not working

[kothandaraman_nagarajan]

Hi,

Try this:

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

BEGIN

IF EXISTS (SELECT T0.[DocNum]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T1.[ItemCode] NOT  IN (select t0.itemcode from OITM T0 where T0.itemcode = 'SF-P')and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and (T2.Series not in ('101') or T2.Series not in ('53')

or T2.Series not in ('164')or T2.Series not in ('103')or T2.Series not in ('55')))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

END

[former_member252592]

you send me same query as before what table is linked with T2 alias because There is no table linked with T2 alias

[kothandaraman_nagarajan]

Sorry for that.

Try this:

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

BEGIN

IF EXISTS (SELECT T0.[DocNum]

FROM OWOR T0  INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]  Left Join OITM T2 on T2.ItemCode = T1.ItemCode

WHERE T1.[ItemCode] NOT  IN (select t0.itemcode from OITM T0 where T0.itemcode = 'SF-P')

and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and (T2.Series not in ('101') or T2.Series not in ('53')

or T2.Series not in ('164')or T2.Series not in ('103')or T2.Series not in ('55')))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

END

[Former Member]

Hi Jamil,

Check this

-/*Block the update of Production Order if Inks & Solvents not Present */

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

BEGIN

IF EXISTS (SELECT T0.DocEntry FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

Left Join OITM T2 on T2.ItemCode = T1.ItemCode

WHERE T0.ItemCode Like 'SF-P%%' and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and (T2.Series not in ('101','53','164','103','55')))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

END

[frank_wang6]

WHERE T0.ItemCode Like 'SF-P%%' and

i think the T0 here should be T1.

[former_member252592]

Thanks for your Reply FRank Wang thats T0 not T1 because i ve multilevel BOM and i required this SP on SF-P Level not on Others

Thanx & Regards

Jamil

[former_member252592]

Thanx for your Reply Bharatiraja its not working Same Result

[Former Member]

Hi Jamil

Did a quick test and it is working, try this:

IF EXISTS (SELECT T0.DocEntry FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry

Left Join OITM T2 on T2.ItemCode = T1.ItemCode

WHERE T0.ItemCode Like 'SF-P%%' and T0.Status = 'R' and

T0.DocEntry = @list_of_cols_val_tab_del and T2.Series not in ('101','53','164','103','55'))

BEGIN

SELECT @Error = 79, @error_message = 'Order Cannot Release Without Inks and Solvents'

END

Kind regards

Peter Juby

[former_member252592]

Thank you for your reply Peter Juby I appriciate it

I've Already Check this SP before post on SCN it Also giving same result as my Posted SP

Thanx & Regards

Jamil