Query / FMS on G/L Account field

[Former Member]

Hi Experts,

Can you help me out on my query / FMS:

Definitions:

1. I created a UDF in Customer Master data: SALES_ACCT_BIR (Alphanumeric,12), ex. 4111-02-1-00

2. I created a UDF in Marketing header: TAX (Alphanumeric, 12, valid values: YES & NO)

3. I assigned my query below in the A/R Invoice GL account (Sales acct) field, refresh when Sales Employee is altered and Tax status = YES

SELECT T0.U_SALES_ACCT_BIR FROM OCRD T0 WHERE T0.OCRD.CARDCODE = $[OINV.CARDCODE] AND $[OINV.TAX] = 'YES'

Purpose: I want to override the GL (Sales) account default when the Tax status is set to YES.

But it seems not working right now, I wonder if this has something to do with the format of my OCRD UDF or for any other reasons.

Hope you could help me out. Thanks.

Don

[Former Member]

Hi Don,

Try this one to see:

SELECT T0.U_SALES_ACCT_BIR FROM OCRD T0 WHERE T0.OCRD.CARDCODE = $[$4.0.0\] AND $[OINV.U_TAX\] LIKE 'Y%'

Thanks,

Gordon

[Former Member]

Hi Gordon,

I tried your query but still it doesn't work, I made some changes, thus:

SELECT T0.U_SALES_ACCT_BIR FROM OCRD T0 WHERE T0.OCRD.CARDCODE = $[$4.0.1] AND $[OINV.U_TAX] LIKE 'Y%'

But still the FMS does not work. I even tried it assigning to Customer Ref No., Due Date as trigger but still to no avail.

By the way, my UDF, U_TAX is YES and NO for values and same YES and NO for description.

Hope you could help me out.

Thanks,

Don

Edited by: Don Elicor on Jan 27, 2010 11:20 AM

Edited by: Don Elicor on Jan 27, 2010 11:22 AM

[Former Member]

Hi Don,

Try this,

SELECT Distinct(SELECT T0.U_SALES_ACCT_BIR FROM OCRD T0 WHERE T0.CARDCODE = $[$4.0.0]) 
FROM OINV WHERE $[OINV.U_TAX] LIKE 'Y%'

Regards,

Madhan.

[former_member204969]

Try this:

SELECT T0.U_SALES_ACCT_BIR FROM OCRD T0 WHERE T0.CARDCODE = $[$4.0.0] AND $[OINV.U_TAX] LIKE 'Y%'

[Former Member]

Check your UDF. It is better if your UDF hold only 0 and 1 to represent No and Yes.

[former_member204969]

May be your reference to your UDF should be $\[OINV.U_TAX] !

[Former Member]

Hi Don.......

Is that giving any kind of error?

Regards,

[Former Member]

Hi,

The field does not have any effect when the tax=yes and after i altered the sales agent. If I try to manually trigger the FMS by doing shift+F2, internal error message is shown in the message bar.

Thanks.

Don