Open SQL / ABAP Statements to use in a INFOSET to create an Open Sales Order Report

[caputog]

Hello experts,

I am in a new position with my company, and my new assignment requires me to dive much deeper into SAP, to the extent that I must learn how to code. I am pretty familiar with the simple QuickView tool and I also successfully create queries via SQ01, SQ02, and SQ03. I really hope to receive inputs from a kind expert who would be willing to help me with this very important task. It would be a great educational opportunity for me.

With that said, I was asked to make an attempt to create a query simulating an open sales order report.

Knowing that SAP already offers VA05 and other tools, my SD associates asked for all a bunch of information that VA05 (and similar reports) do not offer. Given the fact that I am comfortable finding the tables that I need to use for the joins I would like to learn how to implement my idea for data retrieval in the infoset. I would like to use your expertise to read the code, hopefully provided, so that I can start my ABAP training with a tool that would be very useful and at the same time enjoy learning how to code.

With this long and perhaps tedious preamble, I would like to present the idea that I have in mind to create the core of the open sales orders report.

My query would be based on tables like VBAK, VBAP, VBEP, also calling externally for LIPS and VBUK. I won't mention other tables, because I'd like to keep my request of help as simple as possible.

Given the fact that at my company there is the bad practice (in my judgement) to create outbound deliveries far ahead of time and not posting the goods issues sometimes for weeks, the problem that we are facing is that the sales orders affected by, will not be listed in the existing open order reports, because their status is "delivered". This cause Sales Orders and $ amounts not being listed.

Needless to say that we entertained several conversations on this subject, during which I tried to point out the bad practice in place, I was tasked to come up with an alternative.

The idea that I have in mind, and this is what I would like to use your help for is to create a sort of local table that I could connect to VBEP which would have the information of the quantity already delivered by schedule line.

To determine the quantity actually delivered for a sales order (meaning that the goods issues were posted) , I would use a "Select Sum" statement on the fields LFIMG of the table LIPS, where the sales order and the sales order number are the same as the once of VBEP.

To this end, I came up with this code in my infoset:

As you can see, I use the table VBUK to identify the status of the outbound deliveries and my sum, will only include the quantities of the deliveries for which the goods issues were posted.

Now I need to find a way to distribute this value that I calculated across the schedule lines of a sales order. This means that for each record of the table VBEP I need to associate an actual quantity delivered. This way, by evaluating the differences between the confirmed quantities of the schedule lines and the constructed delivered quantities delivered, I would have the quantities that are still pending. Thus, I can do a quick math to calculate the open sales orders quantities and their $ values.

To this end, I imagine I would declare a temporary table like the example below:

So, if I have a sales order situation in table VBEP as shown here ....

.... and my previous Select Sum onto table LIPS in reference to the sales order 15808351/10 tells me that we posted goods issues for a total amount of 200,000 units, my VBELIPS should contain the following:

This means that the quantities of the field QDLVD should be calculated field by field with subsequent subtractions from the total quantity of 200,000 units, equaling the confirmed quantities of the schedule lines of table VBEP, until possible.

Please note that in my example, the values "C" and "I" represent the condition Complete and Incomplete.

The final step would be to link the results of this temporary table to VBEP and produce in output whatever is contained by VBELIPS.

This would be what I intended to be the open sales order report and it would work regardless the outbound deliveries not yet posted.

I do understand that this should be a stand alone ABAP program, but I do have the feeling that it can be done with a query, given the preparation of the correct infoset.

With the hope to receive help I thank you very much.

Gennaro