Fms Query Required in Invoice for Delivery & Document date

[Former Member]

HI Experts,

           Fms query required as per the below requirement .

          1)When Invoice is prepared from SO then Document date & Delivery date of Invoice must be same as SO

          2)When Invoice is prepared from Delivery then Document date & Delivery date of Invoice must be same as Delivery

Regards,

K.Ramasamy

[former_member211473 · Accepted solution]

Hello  Ramasamy

try this for Document Date ( without auto update )

SELECT T0.taxDate

FROM ORDR T0

WHERE T0.DocEntry = $[$38.45.number] And $[$38.43.0]=17

union

SELECT T0.taxDate

FROM odln T0

WHERE T0.DocEntry = $[$38.45.number] And $[$38.43.0]=15

all help from

Regards ,

Ranu

[Former Member · Accepted solution]

      Ranu ,

               I tried fms Manually ..it works fine...For making fms automatic what to do ??

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

the above fms wont suit your requirement

it will pull both dates as he has used union all function

[Former Member · Accepted solution]

Kennedy Sir,

Is it Possible to make fms automatic while copying to invoice from base documents SO , Delivery

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

Try this with

DECLARE @d1 AS DATETIME

DECLARE @d2 AS DATETIME

set @d1 = (SELECT T0.taxDate FROM odln T0 WHERE T0.DocEntry = $[$38.45.number] And $[$38.43.0]=15)

SET @d2 = (SELECT T0.taxDate FROM ORDR T0 WHERE T0.DocEntry = $[$38.45.number] And $[$38.43.0]=17 )

SELECT CASE WHEN $[$38.43.0]=17  THEN @d1 ELSE @d2 end

[Former Member · Accepted solution]

Hi Ramasamy,

This can be possible while copying to invoice from base documents SO , Delivery.Kindly refer the screen shot

[Former Member · Accepted solution]

Bharati raja ,... ur fms query shows error as displayed in image

[Former Member · Accepted solution]

Kennedy sir ,

         The above fms query when updating the document date  it is Empty

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

are you using the copy from po or grpo??

[Former Member · Accepted solution]

Hi Ramasamy,

I have checked in my DB, its working fine and as per your error message in my query i have not used this string $38.1.0.

Check this query

declare @sdocdate date

declare @ddocdate date

set @sdocdate= (select r.DocDate from ordr r where r.docentry=$[$38.45.number])

set @ddocdate= (select r.DocDate from ODLN r where r.docentry=$[$38.45.number])

select case when $[$38.43.number]=17 then @sdocdate else @ddocdate end

[former_member211473 · Accepted solution]

HELLO KENNEDY SIR ,

i have applied  UNION but , every time an A/R invoice is created that will be based on either SALES ORDER or  DELIVERY , So according to my query both the function ( upper and lower part of union ) will never be called at the same time (one part will always not satisfy) . only one will be valid and not the other .. so my query will get  only ONE date  that will be based on either SALES ORDER or DELIVERY. means whatever be the basetype of the A/R invoice.

[KennedyT21 · Accepted solution]

But it will retrieve 2 records one as null hope you got it..

[former_member211473 · Accepted solution]

yes sir one will be null

thanks

Ranu

[Former Member · Accepted solution]

am copying From So to Invoice & Delivery to Invoice

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

That what i mentioned my friend... if the first value is null it is not solved the purpose...

[former_member211473 · Accepted solution]

sir i don't  have that much idea , but when i run this query i get the desired result , either date from SALES ORDER  or from DELIVERY based on  Basetype of A/R Invoices. i thought this way to be a workaround .

thanks

Ranu

[Former Member · Accepted solution]

Hi Ramasamy,

Open AR Invoice Menu and use copy from button to select Sales Order/Delivery and set the FMS query in AR invoice DocDate field and in auto refersh field field keep doctotal field as mentioned in Screen shot.

Note: This will work only when you use copy from button for selecting sale order and delivery.

[former_member211473]

Hii

apply auto refresh on any header level udf which is common in both SALES ORDER and DELIVERY and then you copy that UDF to your A/R Invoice .

Thanks

Ranu

[former_member211473]

hii

did u check the auto refresh part ???  if it works plz do tell me.

thanks

[Former Member]

Hi Ramasamy,

Try this query for both sale order and delivery's document date

declare @sdocdate date

declare @ddocdate date

set @sdocdate= (select r.DocDate from ordr r where r.docentry=$[$38.45.number])

set @ddocdate= (select r.DocDate from ODLN r where r.docentry=$[$38.45.number])

select case when $[$38.43.number]=17 then @sdocdate else @ddocdate end

Regards,

Bharathiraja

[former_member188586]

hi

try this one

SELECT T0.[DocDate] FROM ORDR T0 , RDR1 T1  WHERE  T0.DocEntry = T1.DocEntry AND T1.[BaseRef] =$[ODLN.DocNum]

SELECT T0.[DocDate] FROM ODLN T0 , DLN1 T1  WHERE  T0.DocEntry = T1.DocEntry AND T1.[BaseRef] =$[OINV.DocNum]

and update the Status

[Former Member]

Ramu,

I need this above fms in a same query

Regards,

K.Ramasamy

[former_member188586]

SELECT T0.[DocNum],T0.[DocDate],T0.[CardName],  FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[BaseType] = 17 and  T1.[BaseRef]  =[%0]

[former_member188586]

SELECT T0.[DocNum],T0.[DocDate],T0.[CardName],  FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[BaseType] = 15 and  T1.[BaseRef]  =[%0]

check and update the status...

[Former Member]

Ramu query not working

[former_member188586]

hi try this one

SELECT T0.[DocNum],T0.[DocDate],T0.[TaxDate],T0.[CardCode], T0.[CardName], T1.[Dscription], T1.[FreeTxt],T1.[Quantity], T1.[Price],  T0.[DocStatus] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[BaseType] = 15 and  T1.[BaseRef]  =[%0] and T0.[DocDate] >='01/04/2010'

[Former Member]

Ramu I need the Fms query by both the conditions in a single fms

[Former Member]

Hi,

You may try this first for sales order to invoice only.

SELECT T0.DocDate

FROM ORDR T0

WHERE T0.DocEntry = $[$38.45.number] And $[$38.43.0]=17

If it works, I can add the delivery even though it will be more complicated.

Thanks,

Gordon

[Former Member]

Hi Gordon

              Ya this query is working fine....But the document date is updated manually using fms.. Is it possible to make fms automatic when the document is copied from based document..

Regadrs,

Ramasamy