SAP for Utilities Discussions
Connect with fellow SAP users to share best practices, troubleshoot challenges, and collaborate on building a sustainable energy future. Join the discussion.
cancel
Showing results for 
Search instead for 
Did you mean: 

FICA SAP Query

Former Member
0 Kudos

Has anyone tried building an SAP Query (SQ01) against the FICA transaction tables: DFKKKO (header) + DFKKOPK (GL line items) + DFKKOP (BP line items)?

I'm getting duplicate line items (OPUPK) on my SAP Query.

My INFOSET was created by creating an inner join between DFKKKO to DFKKOPK and DFKKO to DFKKOP. The join is based on document number (OPBEL).

I also tried an INFOSET by joining DFKKKO to DFKKOPK to DFKKOP by doc number. This is also diplaying duplicate line items.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi

You cannot use SAP Query without some ABAP code for this problem. I have tried several solutions, none of it worked, so I had to use ABAP code in the query, in Extras.

I don't know if I can explain why duplicates happen, but I can try if you need to know.

Bogdan

View solution in original post

9 REPLIES 9

Former Member
0 Kudos

Did u try this query ?

select distinct aopbel bOPUPK into corresponding fields of table itab

from dfkkko as a join dfkkop as b

on aopbel = bopbel

join dfkkopk as c

on bopbel = c opbel and bOPUPK = cOPUPK .

Do let me know if this was useful.

Pranjal.

0 Kudos

Thank-you for your feedback Pranjal.

Although I agree that your custom ABAP routine can fix my issue because of the "select distinct opbel" command, I want to create a report via the SAP Query functionality (SQ01/ SQ02) because it offers more flexibility to end-users than a custom ABAP report (i.e. users can create ad-hoc reports off this infoset in the future).

The question really is: Does the SAP Query (SQ01/ SQ02) functionality have the ability to incorporate "select distinct opbel"?

I think the answer may lie with option #4 in SQ02 (create Infoset) where we have the option of creating the Infoset via:

1] table join

2] direct read of a table

3] logical database

4] data retrieval by program

Former Member
0 Kudos

Hi

You cannot use SAP Query without some ABAP code for this problem. I have tried several solutions, none of it worked, so I had to use ABAP code in the query, in Extras.

I don't know if I can explain why duplicates happen, but I can try if you need to know.

Bogdan

0 Kudos

Thanks for your input Bogdan.

I've never added code to an ABAP query, but looks like the functionality is under the Goto/Code menu path under SQ02, and not under Extras.

Can you confirm.

Thanks,

Ken

0 Kudos

Hi, Ken

You are right, the path in the menu is Goto/Code.

I usually press the Extras button and then click the Code tab or directly click the Code tab (the one which looks like a document with a small triangle in the bottom left). Or you can press Shift+F8.

I'm glad I was able to help you and if you need extra help, please let me know.

Bogdan

0 Kudos

Hi Bogdan,

I walked through the debugger with an ABAPer looking for the internal table where we could delete the duplicate records. But we didn't find that table.

Is this the approach you used to delete the duplicate records. If yes, what's the name of the table where you deleted the duplicates.

Thanks,

Ken

0 Kudos

Hi, Ken

It would help if you could describe a little your goal. I'm asking this because the relation between DFKKOP and DFKKOPK is n:m, meaning you may have more or less lines in DFKKOP than in DFKKOPK.

To be a little clearer: for a line in DFKKOP, what exactly do you need from DFKKOPK?

Bogdan

0 Kudos

Hi Bogdan,

The report output I'm looking for is to display only unique line item records from DFKKOP and DFKKOPK.

For instance, if an FICA document had 4 DFKKOP line items and 2 DFKKOPK line items. I'd want the SQ01 query to display only 4 rows (for that doc #):

Row 1: characteristics from line item 1 in DFKKOP &

DFKKOPK

Row 2: characteristics from line item 2 in DFKKOP & DFKKOPK

Row 3: characteristics from line item 3 in DFKKOP

Row 4: characteristics from line item 4 in DFKKOP

I can give you a call if this isn't clear.

Thanks,

Ken

0 Kudos

Hi Ken

It's clear now.

The problem will appear when DFKKOP will have less lines than DFKKOPK. How do you want those lines to be displayed?

The issue is that, although SAP Query is a nice and quite easy tool, it has its limitations. One of them: you are not able to add lines, meaning if you have one line of information and would like to generate 2 lines in the report, you can't.

Of course, you can "trick" the system.

One of the solutions is to have internal tables in which you remember the lines you posted and check each line against that table.

You may have problems when you don't have lines in DFKKOP, but you have lines in DFKKOPK, if the join is not done correctly.

Another problem: DFKKOP lines are split when you make a partial payment, so you may have 3 lines of partial payment for field OPUPK equal to 1.

So, my suggestion:

- do not join like this DFKKKO - DFKKOP - DFKKOPK, but like this:

DFKKKO - DFKKOP
       - DFKKOPK

Both left joins.

- take into account what I wrote above and be very clear in your requirements and specifications, i.e. establish what you will do when you encounter the situations I described earlier.

Please ask me whatever is not clear and give some more information about your goal.

Bogdan