FMS for purchase request

[Former Member]

Hi Experts ,

               i have writte the following query for the alternative item list of the particular item in purchase request i had created the udf U_Alteternative item in the row level field of the purchase request ,please find the following query

SELECT Distinct T0.[ItemCode], T0.[ItemName], T).[Desription],T1.[AltItem], T2.[WhsCode] FROM OITM T0  INNER JOIN OALI T1 ON T0.[ItemCode] = T1.[OrigItem]INNER JOIN POR1 T3 ON T0.[ItemCode] = T3.[ItemCode], OWHS T2  WHERE T3.[ItemCode] = T0.[ItemCode]

i getting the alternative item list of all the items ,i want the alternative item list of particular item only

Thanks and Regards,

Nagendra Prasad

[kothandaraman_nagarajan]

Hi,

Have you tried with standard alternative item function? If you define alternative item under item management, you can easily display alternative items by pressing ctrl + tab at item no.

Thank you.

[former_member211473]

Try this Nagendra

SELECT T1.[AltItem], T3.[WhsCode] FROM OITM T0  INNER JOIN OALI T1 ON T0.ItemCode = T1.OrigItem INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OWHS T3 ON T2.WhsCode = T3.WhsCode  WHERE T0.[ItemCode] = $[prq1.itemcode]

Thanks

[Former Member]

it is showing all the item list

[former_member186712]

Hi,

I think the problem is the OWHS.

Do you need this table?

Regards

[Former Member]

yes sir,i need wharehouse code

[former_member186712]

Try this:

SELECT Distinct T0.[ItemCode], T0.[ItemName], T0.[Description],T1.[AltItem], T2.[WhsCode] FROM OITM T0  INNER JOIN OALI T1 ON T0.[ItemCode] = T1.[OrigItem]INNER JOIN POR1 T3 ON T0.[ItemCode] = T3.[ItemCode], OWHS T2

WHERE T0.[ItemCode] = $[PRQ1.Itemcode]

[Former Member]

it is showing this error

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name 'Description'.. [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

FMS execution failed on field 'U_Alternateitem' with query name '

[former_member186712]

Try this:

SELECT T1.[AltItem], T3.[WhsCode] FROM OITM T0  INNER JOIN OALI T1 ON T0.ItemCode = T1.OrigItem INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OWHS T3 ON T2.WhsCode = T3.WhsCode and T3.ItemCode = T0.itemcode  WHERE T0.[ItemCode] = $[prq1.itemcode]