Formatted Search

[Former Member]

Hi,

I have the below formatted search, which works, to get a gl account from a service call linked to a sales order via a UDF (U_CallID).

select case when ISNULL($[RDR1.BASEENTRY],0) > 0

THEN $[$38.159]

when isnull($[ORDR.U_CalliD],'') = '' then $[$38.159.0]

else

(SELECT U_FormatCode

FROM OSCL (nolock)

WHERE (OSCL.callID = $[ORDR.U_CalliD]))

end as GLAcct

I now need to modify this to only apply the last else statement for when OSCL.CallType in (10,13,15) or OSCL.Customer = 'IDept01'

If the OSCL.Calltype <> 10, 13 ,15 then = $[$38.159.0] needs to apply.

Your assistance will be much appreciated.

Jacques

[Former Member]

Hi Jacques,

I am not quite sure what is your exact request. Check this first:

select case when ISNULL($[RDR1.BASEENTRY],0) > 0
 
THEN $[$38.159.0]
 
when isnull($[ORDR.U_CalliD],'') = '' then $[$38.159.0] 
else 
(SELECT  T0.U_FormatCode
FROM     OSCL T0 (nolock) 
WHERE    T0.callID = $[ORDR.U_CalliD] AND (T0.CallType in (10,13,15) OR T0.Customer = 'IDept01'))
 
end as GLAcct 

Thanks,

Gordon

[Former Member]

Hi Gordon I have tried this without success.

Some background:

We are applying a GL Account (U_FormatCode from the linked service call. This used to apply for all sales orders linked to a service call via the U_Callid. The logic did however change now that it should only be applied for the three call types mentioned earlier (10,13,15) when the U_CallId is <> Null. For all other call types the GL account setup on the Item Master should be applied.

I believe another when statement might have to be added state that if the call types linked to the service call is <> 10, 13,15 then $[$38.159.0] but an not sure how to do this.

I hope this makes more sense...?

Jacques

[Former Member]

Try:

(select case when T0.Customer = 'IDept01'

THEN T0.U_FormatCode

WHEN

T0.CallType in (10,13,15)

THEN T0.U_FormatCode

ELSE $[$38.159.0]
 
END 

FROM OSCL T0

WHERE T0.callID = $[ORDR.U_CalliD]) as GLAcct