cancel
Showing results for 
Search instead for 
Did you mean: 

I need a small query.

Former Member
0 Kudos

hi.

Plz have a look on below image.

i am having some tax types.

in below figure i Selected  Test

in Test it is having some tax types.

what i need is..

i need to capture vat rate.

in below figure

vat rate is 4 ....

i want to run the query base on Tax code  like

Select Rate from table where  taxcdoe = 'Test'

how can i get the

other wise

plz have a look on second image. vat1

i need capture vat4  amount 16

Actually i need  fms at row level at udf

when ever  i select the tax code in tax code vat amount should be copied to the udf value

each tax contains only one vat either 4 or 5 or 6 that amount should come into udf

can i get the value..

I need your valuable suggestions..

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi,

FMS may only run against current form. The Tax Code is not in the same form. That is not possible unless you look up saved value from existing table.

Thanks,

Gordon

Former Member
0 Kudos

Hi. Thanks for your reply.

Actually i need  Fms but title head i wrote i need a query..

Sorry.

Yes through the fms it is not possible.

through the sdk coding i  completed the task.

former_member188586
Active Contributor
0 Kudos

hi

sir check bellow thread

former_member184146
Active Contributor
0 Kudos

Hi,

     try this query instead of previous query

SELECT DISTINCT T0.DOCNUM,T0.CardName,T0.DocDate,T0.TaxDate,T0.DocTotal,T0.VatSum,T5.TaxId11,T3.FormNo,

(SELECT SUM(TaxSum) FROM INV4 WHERE  INV4.DocEntry=T1.DocEntry AND INV4.TaxRate=[%3]) AS VATAmount

FROM ORDR AS T0

INNER JOIN RDR1 AS T1 ON T0.DocEntry=T1.DocEntry

inner JOIN RDR12 AS T3 ON T0.DocEntry=T3.DocEntry

INNER JOIN RDR4 AS T2 ON T0.DocEntry=T2.DocEntry

INNER JOIN OSTA AS T4 ON T4.Code=T2.StaCode

LEFT JOIN CRD7 T5 ON T5.CardCode=T0.CardCode AND T0.DocType=T5.AddrType

where   t2.StaCode=[%1] and t2.TaxRate=[%2]

GROUP BY T0.DOCNUM,T0.CardName,T0.DocDate,T0.TaxDate,T0.DocTotal,T1.Quantity,T0.VatSum,T2.staType,T3.TaxId11,T3.FormNo,T1.DocEntry,T5.TaxId11

Regards,

Manish

former_member184146
Active Contributor
0 Kudos

Hi,

Try this query below

SELECT DISTINCT T0.DOCNUM,T0.CardName,T0.DocDate,T0.TaxDate,T0.DocTotal,T0.VatSum,T5.TaxId11,T3.FormNo,

(SELECT SUM(TaxSum) FROM INV4 WHERE INV4.staType='1'AND  INV4.DocEntry=T1.DocEntry AND INV4.TaxRate=[%3]) AS VATAmount

FROM OINV AS T0

INNER JOIN INV1 AS T1 ON T0.DocEntry=T1.DocEntry

inner JOIN INV12 AS T3 ON T0.DocEntry=T3.DocEntry

INNER JOIN INV4 AS T2 ON T0.DocEntry=T2.DocEntry

INNER JOIN OSTA AS T4 ON T4.Code=T2.StaCode

LEFT JOIN CRD7 T5 ON T5.CardCode=T0.CardCode AND T0.DocType=T5.AddrType

where t2.StaCode=[%1] and t2.TaxRate=[%2]

GROUP BY T0.DOCNUM,T0.CardName,T0.DocDate,T0.TaxDate,T0.DocTotal,T1.Quantity,T0.VatSum,T2.staType,T3.TaxId11,T3.FormNo,T1.DocEntry,T5.TaxId11

Regards,

Manish

former_member188586
Active Contributor
0 Kudos

hi

sir

just look once this thread

Thanks&regards

Andakondaramudu

Former Member
0 Kudos

hi.

Thanks For your Reply.

i am asking is it possible to make fms

before adding the documnet

i think we can capture rdr4

but rdr4 will be filled once add the document only

i need fms.

query i can also do it..

is there any possibility to make fms....

i need only vat 4 tax amount each an every line...