SP for Validating BOM & Production Order item wise

390 Views

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