SP for Validating BOM & Production Order item wise

[Former Member]

Dear Experts,

                    Here I am validating the Production order  items  with BOM for the same items, any addition or deletion in production order should not be allowed. Hence the production order should consist of same items what it has in BOM. But below the my query is allowing this scenario. Kindly help me to achieve the same..

Query Follows :

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

@object_type = '202' and @transaction_type in ('A','U')

if exists (SELECT distinct 'error'

FROM OITT A INNER JOIN ITT1 B ON A.Code = B.Father

LEFT JOIN OWOR C ON C.ItemCode = A.Code

LEFT JOIN WOR1 D ON D.DocEntry = C.DocEntry

LEFT JOIN OITM E ON E.ItemCode = B.Code AND D.ItemCode = B.Code

Where c.DocEntry = @list_of_cols_val_tab_del and d.ItemCode not in (select b.Code from ITT1 b))

Begin

Select @error = N'13002'

select @error_message = N'BOM Mismatch'

END

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

IF @transaction_type IN ('A', 'U')AND @object_type in ('202')

BEGIN

If exists (SELECT T0.DocEntry FROM owor T0

inner join itt1 t2 on t2.father = T0.Itemcode

where t0.DocEntry =@list_of_cols_val_tab_del AND t2.code not in (SELECT T1.itemcode FROM wor1 t1 where t0.docentry = t1.docentry))

Begin

SET @error = '202'

SET @error_message = N'order can not be added if not match with BOM.'

End

END

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Dear Nagarajan,

                       Your query validates well when I delete any item in Production Order, but in the addition of any item which is not in BOM is not validating. Kindly help on this..

Thanks,

Bhoopathi.K

[Former Member · Accepted solution]

Dear Nagarajan,

                       Your query validates well when I delete any item in Production Order, but in the addition of any item which is not in BOM is not validating. Kindly help on this..

Thanks,

Bhoopathi.K

[kothandaraman_nagarajan · Accepted solution]

Hi,

I already checked your previous reply. Let me check and update ASAP.

Thanks & Regards,

Nagarajan