SQVI

[Former Member]

Hi,

I want a report on urgent basis, and though it is simple to extract data from corresponding table and put them on excel sheet, but I prefer to go with SQVI.

I would also request my fellow friends to advice me if any other available resource, which I might have overlooked.

My requirement is I want a report of :

No of sales order created in a week

No of deliveries created on those orders correspondingly

No of deliveries were subsequently billed.

It will be great to have customer no;

I tried to do it by table join method on SQVI with the tables VBAK, LIKP, VBRK, but the system gives message, "no data selected."

first I wonder why, I have also seen the suggestions in forum about outer left join, but not sure how to do it.

Can any one help me to extract my report ?

FYI: I cant go to ABAPER as I am on my own.

Regards,

Venkat

[Jelena · Accepted solution]

I can only guess you just put VBAK, LIKP and VBRK in SQVI and it happily proposed to join them all by VBELN. Well, unless you maintain the same exact number for order, delivery and invoice (which is normally not the case), this obviously won't work.

Link between the documents is maintained at the line item level, so you'd have to link VBAP and LIPS (by VGBEL/VGPOS). If you don't need the actual invoice # then just link VBUP to LIPS and look for deliveries with Billing status = C. I'm assuming you don't do order-related billing since this would be another story.

[Former Member]

My Dear Friends,

simply execute the table VBFA , that will give u all history of sales orders and u may further check the status via VBUK & VBUP , if there is any qty diefferanc found at all line items.

thanks

Mk

[Former Member]

Mahesh,

I was not quite happy with this combination as I my aim is to get sales order no: , delivery no, and billing no: thats all.

Regards,

Venkat

[Jelena]

VBFA might be difficult to use for the OP's needs. If we just do a query by VBFA as single table then it'd return multiple lines, like this:

order delivery

order invoice

But OP wanted to have all the info for the order on the same line. To do this just with VBFA, you'd have to JOIN it with itself (using alias), but this would cause slower performance with no benefit of having other fields available (like with using VBPA, LIPS, etc.). Usually I don't recommend VBFA for queries. It tends to be very large and fields in it are usually not sufficient anyway.

[Former Member]

Hi All,

Thankyou all for the active participation. I have tried various ways, and zeroed on going for the development with the help of ABAPer.

Regards,

Venkat

[moazzam_ali]

Dear Venkat

You gave up very early. I told you you can do this with SQ01,SQ02,SQ03 tool but if you want to go for ABAP then go ahead but this is possible with SAP query tool too but with additional field. I believe you should try with additional fields. It will be a new learning for you.

Thanks

[Former Member]

Hi Moazzam,

I tried with VBAP, LIPS, VBRP did not get desired result, if you can throw some light on how to achieve, I think it will be very useful for my future projects as well.

Looking forward for your suggestion,

Regards,

Venkat.

[moazzam_ali]

Hi Venkat

Have you tried in SQ02 infoset with creatring additional field and using table VBFA?

Thanks

[Former Member]

My Dear Friends,

simply execute the table VBFA , that will give u all history of sales orders and u may further check the status via VBUK & VBUP , if there is any qty diefferanc found at all line items.

thanks

Mk

[moazzam_ali]

Dear Venkat

Ms Jelena has highlighted very important point but I hope you are not doing this .

When we enter all three tables you mentioned system purposed joins of VBELN fields and in every table there is different number so You have to make joins after verifying field values in SE11.

I would like to ask that first you decide what information you required? Joining fields means if there are values in both fields then system will show you data and if there is no value even in any one field of two tables there will be no display for inner join. For outer system will display data but we can only enter one outer join in a report as per my knowledge.

Please tell me you want only those sale orders for which delivery and billing have been done or you want open orders too e.g only orders are created or order and delivery created but no billing.

What if you get maximum data from VBAK and VBUP and for delivery and billing you only get document numbers?? Will this fulfill your requirement?

Thanks

[Former Member]

Hi Mozzam,

I liked your idea, and created ZRPT, with the combination of VBAP and LIPS only.

What i could see in the output is only sales order no: and status complete or not.

The status of the document is show as not relavent for few, and few of them are as "C"

Can you explain about this?

If I want to get sales order no: delivery no; and billing no: is there any way (without ABAPer)

Venkat

[Jelena]

To get the invoice #, you'd also need to join VBRP table to LIPS by VGBEL/VGPOS (confirm in SE16 first). I suggested VBUP because you mentioned that only the invoice count was needed.

Note that if you need to include the orders that are still open, all the JOINs should be LEFT JOIN. Otherwirse it'll include only records with matching entries in all the tables involved. Left join essentially means - give me the record from another table, if it exists, but if it doesn't, still include the record from the first table (fields referring to another table will just be blank).

Also the fields from the tables joined with LEFT JOIN should not be on the selection screen. If you try to enter any selection criteria for them, LEFT JOIN will behave as a regular JOIN.

[moazzam_ali]

Hi

Can you use sq01 sq02 sq03 instead of sqvi? If yes then I will give u exactly what you need.

[moazzam_ali]

Note that if you need to include the orders that are still open, all the JOINs should be LEFT JOIN. 

Dear Ms Jalena

Can we use all joins as outer join sqvi or sq02? Whenever I tried this system always gave error that u can use only one join as outer. Please put your expert views on this.

Thanks

[Jelena]

Ah, forgot about that - sorry. Good catch. It does not allow to "chain" left joins, unfortunately. So unless OP is OK with not including the open orders (i.e. with no deliveries) this actually won't work as just one query in SQVI. SAP Query (SQ01/SQ02) has more functionality available than SQVI.

[moazzam_ali]

Dear MS Jelena

Yes, I am totally agreed with you. To write in single line three documents e.g sale order, delivery and billing is not possible with SQVI thats why I asked OP that whether he can use SQ01,SQ02,SQ03. Because in these we can create additional fields and can fetch data as per our requirement with some logic and coding but thanks for sharing your Idea. Your suggestions are always good

Thanks

[former_member184065]

Dear,

Please put one eye on below screen shots .I have created whatever you want tables to join for getting report by SQVI.Make sure ,select both of fields when you are going to create SQVI Report as marked in Yellow color .When you select both fields then only you can create report .

If you have any doubts please ,go and see videos of SQVI in YouTube .

Thanks,

Naren

[Former Member]

Hi Naren,

I tried exactly as you adviced, and no result yet.

Please see the screen shots below

Please let me know what could be the reason.

Regards,

Venkat