Query for FMS in Order Recommendation page

[Former Member]

Hi

I have created a UDF in the MRP order recommendation page in which I want to show the quantity on order (ordered but not yet received) from the inventory tab in the item master data.

I am not having success so far in writing the correct query , can someone please help

We are running SAP B1 v8.81

[Former Member · Accepted solution]

Hi,

Try:

Select T0.onorder from oitm

where T0.itemcode= $[orcm.itemcode]

Thanks,

Gordon

[Former Member · Accepted solution]

Hey guys

thank you all for your replies ,

I tried all the queries suggested and they gave me an error when I tried to execute them.

this is without even going to the FMS and the UDF

please note that I am completely new at this

I have tried below query which executes fine and it has listed all the items we use when I ran it.

the first item in the reults of the query has a qty of 24

However when this query is called in the FMS in the MRP order page it only returns the first value (24)  on all the rows and items in the order page

any suggestions??????????

SELECT T0.[OnOrder] FROM OITM T0  INNER JOIN ORCM T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[ItemCode] = T1.[ItemCode]

[KennedyT21 · Accepted solution]

Hi Sham,

Try This .

SELECT (OITW.OnOrder) FROM OITW

WHERE (OITW.ItemCode = $[orcm.ItemCode] )

and  (OITW.WhsCode = $[orcm.warehouse] )

Check with your UDF Type as UNITS &TOTALS -- QUANTITY

Regards

Kennedy.T

[Former Member · Accepted solution]

Hi and thanks again for all your help

unfortunately still all these queries are generating errors.

I am just copying your suggestions and pasting it in the query

is this the right way?

does anybody know why the FMS of the query i wrote is only returning one value ("24" from the table in the picture)

[KennedyT21 · Accepted solution]

Hi Sham,

What Is your UDF Type, I have tested it working fine for me.

Just Copy the query and save in Query Gen.( It wont Run in the Query gen)

In the desired field assign the formatted Search. Do not apply any automatic refresh.

Regards

Kennedy

[Former Member · Accepted solution]

Hi Sham.........

Try this.........

Select T0.OnOrder From OITM T0 Where T0.ItemCode=$[$3.7.0]

Hope this will work.......

Regards,

Rahul

[Former Member]

The query you wrote is not a FMS query. A FMS query cannot be run under query manager. You have to define FMS first.

[Former Member]

Hi Gordon

you are right, I did not know it doesnt need to work in the query manager

I have tried the query suggested by Rahul (see below) and it did work and solved the issue,

Thank you Rahul, Gordon and everybody

Select T0.OnOrder From OITM T0 Where T0.ItemCode=$[$3.7.0]

[KennedyT21]

HI Sham,

I think its not possible but  am not sure.

check with this tread

http://scn.sap.com/thread/2040177

Regards

Kennedy

[Former Member]

Hi Kennedy

just to clarify it a bit , I want to show the existing ordered quantity of each item in the order recommendation page itself not in the purchase or production order that I want to issue

[KennedyT21]

Send u r FMS query what you have tried.

Regards

Kennedy

[KennedyT21]

Try With this

SELECT (OITW.OnHand) FROM OITW

WHERE OITW.ItemCode = $[orcm.ItemCode] AND OITW.WhsCode = $[orcm.whscode]

Regards

Kennedy

[former_member184718]

Hi Sham,

Try this query:

Select distinct oitm.onorder from oitm

where oitm.itemcode= $[orcm.itemcode]

Thanks.

Hari