How to use a query in the Print Layout Designer?

[Former Member]

I created a query with the query generator:

SELECT T0.[Discount], T0.[NumOfDays], T2.[DocNum] FROM CDC1 T0, OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

The result shows the cash discout (the days and the percentage) of every record.

I need this query, because I want to design a sales order report.

To show the payment conditions with cash discount I need to have the table CDC1 but when I create a new database field in the report I can't see that table.

So I queried the table CDC1....

Then I created a new database field and in the list box "table" I made the following steps:

extras\customizing tools\user defined values - definition\search for saved query in user defined values

But the problem is that I don't get the result of the query into my report.

What's the problem? I don't know what I've done wrong...

please help me

[Former Member · Accepted solution]

So, I took the following queries of you:

SELECT T2.NumofDays FROM OCTG T0 INNER JOIN OCDC

T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode

WHERE T0.GroupNum = $[ORDR.GroupNum]

SELECT T2.Discount FROM OCTG T0 INNER JOIN

OCDC T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode

WHERE T0.GroupNum = $[ORDR.GroupNum]

I created 2 UDFs in the title of marketing documents and set up the formatted search with the queries.

I choosed 'Auto refresh when field changes', the field 'payment terms code and 'refresh regulary'

But now I get an internal error [-1004] [message 131-183]

[Former Member · Accepted solution]

Is it $http://ORDR.GroupNum or

$[ORDR.GroupNum]

?

[Former Member]

Oh, I'm sorry. It is $[ORDR.GroupNum]

[Former Member]

The field "NumofDays" is definitely the correct one. I checked the query result and also the system information showed me, that the field names "NumofDays".

The problem of your query was $[ORDR.GroupNum] -- it causes an error message, just as you say...

I will try out your queries again and tell you the result.

[Former Member]

I made the following queries:

SELECT T0.[NumOfDays] FROM CDC1 T0 , OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

SELECT T0.[Discount] FROM CDC1 T0 , OQUT T2 INNER JOIN OCTG T3 ON T2.GroupNum = T3.GroupNum WHERE T2.[GroupNum]=T3.[GroupNum] AND T0.[CdcCode] = T3.[DiscCode]

but if I change the payment conditions in the order/sales the field doesn't update itself.

[Former Member]

Meike,

1. Is it the column NumofDays or Day in CDC1 where you have the number of days? I think it should be the Day.

2. Your queries don't seem to be correct, why don't you use mines? If you run them from the query generator you will receive an error message but that's fine as they will trigger information from the order which is opened on your screen. Don't worry about the error just save them and then assign to the formatted search, they will work fine!

[Former Member]

I created the queries successfully. But how can I set the formatted search in the UDFs on auto populated?

[Former Member]

When you set up the FS, choose 'Auto refresh when field changes' and choose the field 'Payment Terms Code', then 'Refresh regularly'.

Please let me know if it solves the issue.

[Former Member]

Well, I tried it out but I guess it is too complicated for the sales department to select every time the cash discount days and percentage.

Is there no other way to get the CDC1 table into the list box of the database field in PLD?

[Former Member]

There is a way but:

1. it is not supported by SAP,

2. it will not make your life easier as then you need to create a lots of formulas.

You can set the formatted search into the UDF's to be auto populated so the sales dept don't need to choose it every time.

[Former Member]

The query for the Days for the formatted search could be something like:

SELECT T2.Day  FROM OCTG T0  INNER JOIN OCDC
 T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode 
WHERE T0.GroupNum = $[ORDR.GroupNum]

and for the Discount:

SELECT T2.Discount  FROM OCTG T0  INNER JOIN 
OCDC T1 ON T0.DiscCode = T1.Code INNER JOIN CDC1 T2 ON T1.Code = T2.CdcCode 
WHERE T0.GroupNum = $[ORDR.GroupNum]

[Former Member]

Hi Natalia,

I have a similar problem of a missing table in PLD and I would like to know more about the solution that exists.

I actually need to display credit cards details (RCT3) on the incoming payment (ORCT) layout, and this table is not available.

Thanks in advance for your help,

Remy

>

> There is a way but:
> 1. it is not supported by SAP,
> 2. it will not make your life easier as then you need to create a lots of formulas.
> 
> You can set the formatted search into the UDF's to be auto populated so the sales dept don't need to choose it every time.

[Former Member]

Remy,

Please open a new thread with your question, I'm sure the forum members will help you.

Thanks,

Nat

[Former Member]

Hi Natalia,

Thanks I'll do that.

Remy

[Former Member]

Hi,

I am creating the layout for a sales order which will be send to a customer.

In that order the customer can see the payment conditions.

It should look like this:

-

---

net price: 1000,00 EUR

19,00% taxes: 190,00 EUR

gross price: 1190,00 EUR

payment conditions:

payable within 30 days net: 1190,00 EUR

payable within 14 days with discount of 2 %: 1166,20 EUR

-

---

to get the bold marked digits I need to have access on the following tables:

OCTG and CDC1

In the OCTG table I get the days (in this example 30)

In the CDC1 table I get the days of cash discount and the percentage (in this example 14 and 2)

but in the sales order layout in PLD only the OCTG table is shown when I create a database field and the CDC1 table isn't.

So what can I do to get this?

[Former Member]

I don't think it's possible simply through PLD.

What you can do is to create User Defined Fields on the Sales Order, and set up Formatted Searches in them to display the days and percentage. Then you can display these UDF's on the layout from the Sales Order table itself.

Hop it makes sense.

Regards,

Nat

[Former Member]

Meike,

You are trying to add a Formatted Search in the 'Table' list box in PLD. It will not work...

If I understand you, you would like to display the result of the query on your print layout? For which transaction are you creating the layout?

Regards,

Nat