Regarding JOIN Statement

[former_member267445]

Hi Gurus,

I am working for change TR. I need to get the data from 2 tables and the table are VBEP and VBBE. Scenario is business will create some orders(future orders) with Schedule line category with out confirmed quantity, so those orders(future orders) will not store in VBBE. After confirmed some quantity the schedule line item category will change then those orders will store in VBBE. But business needs both the orders which stored in VBEP and VBBE.

Here the fields in selection screen are Material Number, Plant, Customer Number, these are not in VBEP but in VBBE.

Previously the report was developed by using selection from VBBE but in output those store in VBEP orders(future orders) are in not displaying.

Please light me how to join these tables.

Thanks and Regards,

Muralikrishna Peravali

[Kartik2]

Hi,

I think the BAPI 'BAPI_SALESORDER_GETLIST' will work for you. Please try it and let us know. Thank you.

Regards,

Kartik

[former_member267445]

But i want to implement this query in already existing Z report without disturbing functionality.. Can I?

[ThomasZloch]

So there is always an entry in VBEP but not always in VBBE? Then try a LEFT JOIN between these two tables with VBEP as leading table, linked by the identical primary key.

Recent similar discussion:

Thomas

[former_member267445]

Hi Thomas,

Thank you for reply. But i need the data from VBBE also. As per my knowledge if we use LEFT JOIN then right side table data wont get if there is no similar record.  Correct?

Regards,

Muralikrishna

[ThomasZloch]

Correct, but the VBEP data would be there. What data would you expect if there is no linked VBBE record?

Maybe I did not understand your scenario correctly. Are you saying there might be entries in VBBE for an order item but not in VBEP? It's been a while since I worked in SD, is this possible at all considering the application logic?

The cardinality must be clear, only then a working solution can be proposed.

Thomas

[former_member267445]

Hi Thomas,

Actually the scenario is.. we have future order concept in SD. for Future order they created schedule line category type as ZF, when they create the order with ZF then the order is not updated in VBBE table. But the order is in VBEP with ETTYP = 'ZF'.

If any user confirms the quantity then schedule line category converted as CP that means MRP, the order with CP is stored in VBBE. Business wants in output the orders which have ZF and as well as CP.

Thanks and Regards,

Muralikrishna Peravali

[ThomasZloch]

In the second case, is there still an entry in VBEP, in addition to VBBE? (I assume so)

If yes, you can go back to my first suggestion, select VBEP and left join VBBE based on VBELN, POSNR and ETENR.

Thomas

[former_member267445]

Hi Thomas,

If i write, select from VBEP left outer join VBBE. Then I am not able to write the conditions in WHERE clause using VBBE. Here I enclosed the query..

Thanks and regards,

Muralikrishna.

[alex_campbell]

If you want to select orders that have type ZF, than it does not make sense to include any conditions for table VBBE in the WHERE clause, because orders that have type ZF will not have any value for those fields.

It sounds like you are trying to select two exclusive datasets. Records where VBEP~ETTYP = 'ZF', and records where VBEP~ETTYP = 'CP' AND VBBE~??? = the value you allude to needing to check. You could break it into two different select statements (one for ZF, the other for CP), and use APPENDING TABLE instead of INTO TABLE

[JackGraus]

To me it looks like that data that is comming from VBBE (Material numer, Plant, customer number) should be get from other tables in case there is no VBBE entry for a VBEP schedule line. These tables are VBAK (Order header) and VBAP (Order item). When you have a VBEP schedule line there is always an order VBAK and order line VBAP.

Regards Jack

[former_member267445]

Hi Jack,

Sorry for late reply, I was on leave. I have quantity fields that should get from VBBE only. I searched related fields but i didnt find.

Regards,

Muralikrishna

[former_member267445]

Hi Thomas,

I tried usinf left join, till the orders are not coming whose type is ETTYP = 'ZF'.

Regards,

Muralikrishna