join on LIPS and EKPO for open items

[Bharath84]

Hi All,

I need to fetch the Vendor,Delivery,Delivery Line Item, Identification, Material,Reference Doc no(PO #), Ref Item(PO Line no), Receiving Plant (Shipping notification), Bill of Lading (Shipping notification Header) from tables LIPS and EKPO where in I have to display the Open items from EKPO.

How to join LIPS and EKPO tables as the length of LIPS-POSNR and EKPO-EBELP are different. Is there any other way to fetch the data?

Awaiting your replies.

Thanks,

Haritha

[rajucd81 · Accepted solution]

Hi

Use LIPS-POSNV (Originating item) = EKPO-EBELP.

Thanks

Raju C D

[Bharath84 · Accepted solution]

Hi,

The length of POSNV(6) and EBELP(5) are not the same. so how can you compare these two fields?

Thanks,

Haritha

[Former Member · Accepted solution]

Please refere to this thread

http://scn.sap.com/thread/1111768

There is an helpful answer

[former_member330132]

after seeing this answer for years I am pleased to announce there now finally seems to be a fix for this in 7.50

inner join lips on lips~vgbel = ekko~ebeln and substring( lips~vgpos, 2, 5 ) = ekpo~ebelp

[hajduk]

Thanks a lot

[lat_gmbh]

Thank you. That works.

[former_member738480]

Hi vidya.p ,

I would suggest [ append a zero in front and pass ] you to include the function lpad(ebelp , 6 , '0')

the 6 denotes the length that needs to be there since its std length is NUMC5 as vgpos has NUMC6
'0' => Digit that needs to be appended
Now after this Exact syntax syntax given below EBELP Acts as NUMC6 and the Logic works 🙂

Example :

lips.vgpos = lpad( ekpo.ebelp , 6 , '0')


Regards,

Akash R

[hajduk]

What do you mean with include lpad function? i can use lpad function but not in join clause.

[Sandra_Rossi]

hajduk You'd better ask a separate question, many people could help, it's better than one. Also indicate your ABAP version.

[former_member201275]

Have a look at pages 23 and 25 of this relations table (one of my favourites), I think this is what you are looking for:

http://ddd.uab.cat/pub/trerecpro/2007/hdl_2072_5419/PFCLopezRuizAnnex3.pdf

The other recommendation I have is to download ztablescope, which, if I remember correctly is available somewhere here on scn. This really helped me to find table links.

[Former Member]

Hi Haritha,

Declare a variable 'X' of char type of length 10.

Then use MOVE statemnt:

MOVE 'POSNR' into X(Character Field).

Regards,

dipti.