cancel
Showing results for 
Search instead for 
Did you mean: 

I need a Small FMS query.

Former Member
0 Kudos

hi.

i need a small fms query i am not able to do it.

what is my requirement is

i create one Udf field which is amount type at sales order rows.

and in item master i enter some values at factor1 under sales data.

when ever i pick the item at sales order row level..

i need a calculation like

row level i enter like this

itemcode      manual price(udffield)

001                  25


001  is having factor1 value at item master is 5


then i need calculation like


select ((t2.SalFactor1 *100/100)*(t1.U_Manpric)) as ManualPrice from ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry inner join oitm t2 on t2.itemcode = t1.itemcode where t2.ItemCode ='00201010001'

i need above calculation

i tried

select ((t2.SalFactor1 *100/100)*($[$t1.U_Manpric.0.0])) as ManualPrice from ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry inner join oitm t2 on t2.itemcode = t1.itemcode where t2.ItemCode ='00201010001'

but not working

Any information plz update me.



Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Srinivas,

Check below FMS.

select ((t2.SalFactor1 *100/100)*($[rdr1.U_Manpric.Number])) from ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join oitm t2 on t2.itemcode = t1.itemcode

where t2.ItemCode = $[rdr1.ItemCode]

Hope this helps

--

--

Regards::::

Atul Chakraborty

Former Member
0 Kudos

I am checking Plz wait

After that i will close it..

Former Member
0 Kudos

hi.

thanks for your reply.

I changed your query to

select ((t2.SalFactor1 /100)*($[rdr1.U_Manpric.Number])) from ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join oitm t2 on t2.itemcode = t1.itemcode

where t2.ItemCode = $[rdr1.ItemCode]

i remove *100  i need percentage

in some rows the value is not reflecting

may i now why.

KennedyT21
Active Contributor
0 Kudos

Hi Srinivas..

What is the error , press shift + F2 on the values not filled rows and check what error is coming..

Rgds

Kennedy

Former Member
0 Kudos

hi.

Kenady t

thanks for your reply.

if i press picker it is showing nothing i mean   no error is comming

values is 0.0  is coming.

if i press that line i mean i just put the cursor on the line empty line if i open the query at user defined query

no data was found coming..

may i know why it is comming

if i see the item master it is having factor 1

why it is comming i dont know.

i am checking diff diff scenario

i have some questins

1) is the item should have instock

    i was thought earlier this is problem 

    but some items are having stock but still above problem is comming

2) if the item is belongs to service , is above will work.

    i think it should work.

   but in that point also i am checking.

   plz update me what's wrong..

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

select ((t2.SalFactor1 /100)*($[rdr1.U_Manpric])) from ordr t0

inner join rdr1 t1 on t0.docentry = t1.docentry

inner join oitm t2 on t2.itemcode = t1.itemcode

where t2.ItemCode = $[rdr1.ItemCode]

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi

your request is not so clear .

1.you expect the manualprice to be 25 and the factor is 5 so is the price should be 5*25 ?

2. where the manualprice came from ? is .U_Manpric came from the item ?

shachar

Former Member
0 Kudos

hi.

thanks for your reply.

Sales order At Row level

i create manualprice

(itemmasterfacto1/100*Manualprice)

above factor1 should come from item master under sales data..

Former Member
0 Kudos

I tried below query..

Select ($[oitm.SalFactor1 ] * $[rdr1.U_Manpric.number]) from rdr1 t1

inner join oitm t2 on t2.itemcode = $[rdr1.itemcode]

but not working.

Former Member
0 Kudos

Any information plz update me.

KennedyT21
Active Contributor
0 Kudos

Hi Srinivas..

Try This

Select $[rdr1.SalFactor1 ]*  $[rdr1.U_Manpric] from rdr1 t1

inner join oitm t2 on t2.itemcode = t1.itemcode

Regards

Kennedy

Former Member
0 Kudos

hi.

Kennady thanks for your reply.

small problem

i has to catch sales factor1 from item master not from rows

ok the customer has to enter qty at sales order rows so,

if they enter factor is changing.

i need to catch from item master ..

KennedyT21
Active Contributor
0 Kudos

Try This

Select SalFactor1 *  $[rdr1.U_Manpric] from rdr1 t1

inner join oitm t2 on t2.itemcode = t1.itemcode