SO Item code field query

[Former Member]

Dear Friends,

I would like to get some assistance in writing a query for the following scenario,

I have created a couple of UDF's in the BP master and linked them to U_Class table. This U_Class table is a OITM table.

Each BP master data has a vaue in the UDF.

ie U_Class_1 = 01

U_Class_2 = 02

When generating a SO I would like to run a FS in the Item code field validating the BP master UDF field's to the Item Master U_Class field and populate only the items that belong to that particular UDF's.

I would really appreciate if you could help me to write the query

Regards,

Joel

[Former Member]

Posting my query on SDK forum.

[Former Member]

Try this one:

SELECT T0.U_Class

FROM DBO.OITM T0, DBO.OCDR T1

WHERE T0.ItemCoce = $[$38.1.0\] AND T1.CardCode = $[$4.0.0\] AND T0.U_Class in (SELECT T1.U_Class_1, T1.U_Class_2)

[Former Member]

Dear Gordon,

I tried it but getting an error saying incorrect syntax near $[$38.1.0]

SELECT T0.[U_Class] FROM OITM T0, OCRD T1 WHERE T0.[ItemCode] = $[$38.1.0] AND T1.[CardCode] = $[$4.0.0] AND T0.[U_Class] IN (SELECT T1.[U_Div_1], T1.[U_Div_2])

Regards,

Joel

[Former Member]

This query could not be run directly from query manager. It can only run as FMS on UDF.

Some changes are need for the last part:

(SELECT U_Class_1, U_Class_2 FROM OCRD WHERE CardCode = T1.CardCode)

[Former Member]

Is it possible to run this FMS on Item code field of the SO?

[Former Member]

No. What is your logic to run it on the item code field if you need to check in the item level?

[Former Member]

Ok. my logic is when I run the FMS on the Item code field the FMS will display the Item codes that belongs only to that particular customer.

the BP master has the UDF values and then it will cross check with the Item master and pull only those matching values (item numbers/class).

If not then all the items.

Please let me know if I am not clear..

[Former Member]

Try this first:

SELECT T0.ItemCode

FROM DBO.OITM T0, DBO.OCRD T1

WHERE T1.CardCode = $[$4.0.0\] AND T0.U_Class in (SELECT U_Class_1, U_Class_2 FROM OCRD WHERE CardCode = T1.CardCode)

[Former Member]

I tried but getting the same error incorrect syntax near $4.0.0

What is DBO.OCDR table?

Thanks,

Joel

[Former Member]

Sorry. It should be OCRD

[Former Member]

Hi Gordon,

Still getting the error.

[Former Member]

Are you using U_DIV_XX or U_Class_XX in BP master?

[Former Member]

I am using U_Div_1 and U_Div_2 in the BP Master and I have changed accordingly in your query.

SELECT T0.ItemCode

FROM DBO.OITM T0, DBO.OCRD T1

WHERE T1.CardCode = $[$4.0.0] AND T0.U_Class in (SELECT U_Div_1, U_Div_2 FROM OCRD WHERE CardCode = T1.CardCode)

[Former Member]

Dear Joel,

Please clarify what you mean for: This U_Class table is a OITM table. Do you mean it is an UDF in the OITM table?

Thanks,

Gordon

[Former Member]

Yes .it is a UDF in OITM table.