Reproduce "Invoice Report" in cic0 - printed document line items with net amount

[iliasgi]

Dear experts,

I'm having a hard time trying to re-create a table that resembles the "invoice report" on cic0 from SAP ISU (Utilities) and will be used to show all the printed line charge names with its amounts that are also present in the printed invoice document.

I know that the tables ERDK - DBERDL - DBERDLB shall be used (and presumably also Dberchz1 - dberchz3 - erch - zqcr_ageing_cust) but I was not able to match it with the "invoice report".

Could anyone kindly provide me with the related SQL code or if not possible what should I filter out/join/union so I will be able to reconstruct it?

Many thanks in advance and I'm sorry if my question violates any policy.

[moshenaveh]

Welcome to the SAP Community! Thank you for visiting us to get answers to your questions.

Since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members. Secondly, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will help you when submitting questions to the community.

Finally, I recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

[former_member587072]

Hello ... I'm not familiar with Invoice Report but based on the description 'printed document lines items with net amount', I'd approach it like this:

- It's not clear if the report would be run for a given account or a wider selection but either way, inner join ERDK to ERCHC (on opbel) to get the invoice to billing document relationship (selecting from erdk on whatever is appropriate for the requirement)

- then use those billing documents when doing an inner join of DBERCHZ1 to DBERCHZ3 (on belnr/belzeile). dberchz3 to get the net amount and dberchz1 to do the proper selection on document line item type and/or posting relevant (not sure if you're looking for billed only or budget dollars).

- If open FICA 'current' lines are required, then also use DBERDL (for example, syncdd line item type)

Regards ... Joe

[iliasgi]

Hello Joseph and thank you so much for your reply.

I found out that if I just use the tables ERDK - DBERDLB & DBERDL (union Dberdlb & Dberdl on columns printdoc / printdocline / nettobtr) and then sum nettobtr on printdoc level then it would 100% match with the header table ERDK column Total_AMNT which is indeed the total amount of any printed invoice. With that in mind I'm not really sure if DBERCHZ1 and DBERCHZ3 is really needed at this time.

The problem with that is that each row represents a line item yet I'm not able to map a line item with the charge name that is printed on the document. I am observing the columns HVORG & MWSKZ on dberdlb and columns HVORG TVORG BELZART on dberdl may contain some mapping information that I could join with another table with their respectives names but I have not found it yet nor totally sure that I am in the right direction.

Regards,

[former_member587072 · Accepted solution]

Hey Steve ... Using dberdlb (as opposed to dberchz1/dberchz3) does limit the information you have available. As you note, no line item type (belzart) ... depending on the report detail, no rate category (tariftyp) or from/to date (ab/bis), for example. That is why I usually use erchc to make the print document to billing document connection and then get the billing document details from dberchz1,3 (sometimes 2).

With that said ...

- the text for the belzart from dberdl can be found in TE835T (line item type text) for the required language

- and not perfect but the text for the hvorg in dberdlb can be found in TFKHVOT (main txn text) for the application area 'R' and the required language

- or many times the print team will create a Z table for the text used on the invoice with either a line item type or main/sub combination key.

Hope that helps ... Joe

[iliasgi]

Thanks Joseph, I really appreciate your help! You are right, indeed, dberchz1 is needed to get the mapping of TVORG as well as BELZART. without that there is no way to map the related printed lines.

Even though I searched for the tables TE835T & TFKHVOT, and they partially answer my needs, they do not have the exact map that I am looking for (its more high level grouping which these tables do not contain). Nevertheless, I believe I'm really close to it, but probably there is a custom table (probably Z table as you mentioned) that the print team uses.

By any means, do you know if there is a way to search for such custom tables? I am trying through SE16 (there is an option to find tables that contain specific characters or by short description), but I still havent figured it out. Maybe there is a more sophisticated way?

[former_member587072]

If the print team created a z table for line item type descriptions they are most likely reading it to an internal table in the start or before doc_header exit of the application form. Go to EFRM, select the invoice application form, display the hierarchy and click on the start and/or before doc_header exit to review.

Regards ... Joe