Freight Breakup of A/R Document in PLD

[former_member209707]

I need Freight Breakup of A/R Document in PLD.

I have created multiple freight seeting in the the Freight Setup.

When Adding these values in A/R Invoice I need the breakup in PLD

Please help.

[former_member187989 · Accepted solution]

hi,

Add new repetitve area in PLD.

Create a new database field from table OEXD - Freight setup,select field - Name

Create a new database field from table INV3- AR Invoice Rows - Line Total.

Sort repetitive area,tick summary.

Detick visible for both fields in repetitive area.

Add a formula field with formula SortValue() in repetitve area footer mention it in group 1 in general tab of field.

Add a formula field with forumual ColSum(LineTotal) in repetitve area footer mention it in group 1 in general tab of field.

Hope it solves problem.

Jeyakanthan

[former_member209707 · Accepted solution]

Your reply was nice and it worked to give the breakup.

But the big issue is that, every breakup comes on a seperate field.

[gagan_thareja · Accepted solution]

Dear Aslam,

Could you please try the following:

1. Create UDFs on Marketing Document header level - number of UDF

depending on the max number of Additional Expenses from the OEXD

2. Use query like below for UDF1:

SELECT T2.EXPNSNAME + ' ' + STR(T1.LINETOTAL) + ' ' +

STR(T1.VATSUM) + ' ' + STR(T1.VATPRCNT)

+ ' ' + STR(T1.LINEVAT)

FROM OINV T0

INNER JOIN INV3 T1

ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN OEXD T2

ON T1.EXPNSCODE = T2.EXPNSCODE

WHERE T0.DOCNUM = $OINV.DocNum

AND T1.LINENUM = 0

Use query like below for UDF2:

SELECT T2.EXPNSNAME + ' ' + STR(T1.LINETOTAL) + ' ' +

STR(T1.VATSUM) + ' ' + STR(T1.VATPRCNT)

+ ' ' + STR(T1.LINEVAT)

FROM OINV T0

INNER JOIN INV3 T1

ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN OEXD T2

ON T1.EXPNSCODE = T2.EXPNSCODE

WHERE T0.DOCNUM = $OINV.DocNum

AND T1.LINENUM = 1

and so on.

NOTE: The line number is important as there can be more than

one additional expense for the same Document Number;

Note that the query draws the details of one single record per

from the Add Expenses Table. You can make the amendments in the

query as required by either removing or adding more fields;

like increase space between the fields etc

3. Add the UDF to the Layout.

Hope this will help.

Regards,

Gagan Thareja

SAP Business One Forums Team

Edited by: Gagan Thareja on Apr 6, 2009 3:08 PM

[former_member209707 · Accepted solution]

Dear Gagan Thareja,

The query you send is right.

The big issue which i am unable tyo understand is when one of my collegue tested it on a differnet server, it works still it does not work on my server.

The difference in boths ervers is His is SAP B1 2005B pl 44 and I am using SAP B1 2007B pl 08.

Can you clarify som this in this regards.

[gagan_thareja · Accepted solution]

Dear Aslam,

Even I had tested this query in 2005 Version and this query is 100% correct.

Probably if you have 2007 Version already installed so you could check in that. As there could be difference of the fields naming in both versions.

You can get the field naming via View-> System Information.

Hope this would help.

Regards,

Gagan Thareja

SAP Business One Forums Team

Edited by: Gagan Thareja on Apr 13, 2009 5:23 PM