04-28-2008 9:23 PM
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.
05-02-2008 7:39 PM
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
04-29-2008 6:24 AM
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.
04-29-2008 3:36 PM
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
05-02-2008 7:39 PM
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
05-13-2008 3:01 PM
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
05-14-2008 8:10 AM
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
05-14-2008 9:50 PM
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
05-15-2008 9:07 AM
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
05-15-2008 11:36 PM
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
05-16-2008 8:35 AM
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