ABAP HCM : Data from 2 tables

[Former Member]

Hi.

I want to retrieve 5 fields from table pa0169.

The problem is I also need to check if the Company Code field (pa0001-bukrs) for the employee is one of the specified values.

The company code values are maintained in range r_division with low values.

The Plan_ID values are maintained in range r_plan_id with low values.

Initially I was getting multiple entries for a pernr in the resulting table. Hence I have added the SORT and DELETE DUPLICATES commands.

The Function spec snippet :

Select all employee's where Benefit Plan (P0169-BPLAN) equals Custom Parameter 'Plan ID' Low

Value as of the Parameter 1 date and where Company Code (P0001-BURSK) equals Custom Parameter 'Division

Name' Low Value,

Please tell me if this will work? Also tell me the performance of this query.

SELECT A~pernr

A~bplan

A~begda

A~endda

A~aedtm

FROM pa0169 as A

INNER JOIN pa0001 as B

ON A_{pernr = B}pernr

AND A_{begda >= B}begda

AND A_{endda <= B}endda

INTO TABLE oi_0169

WHERE A~pernr IN r_pernr

AND A~bplan IN r_bplan

AND B~bukrs IN r_division

AND A~begda <= v_parm1date

AND A~endda >= v_parm1date.

SORT oi_0169 BY pernr ASCENDING begda DESCENDING.

DELETE ADJACENT DUPLICATES FROM oi_0169 COMPARING pernr.

Thanks & Regards

Edited by: SUD239492 on Feb 11, 2011 5:17 AM

[Former Member]

Hi,

Split the join and write as :

select pernr endda begda into table it_pa0001

where pernr in r_pernr

AND endda >= v_parm1date

AND begda <= v_parm1date

AND bukrs IN r_division .

if sy-subrc = 0.

sort it_pa0001 by pernr begda descending.

delete adjacent duplicates from it_pa0001 comparing pernr.

SELECT pernr

bplan

begda

endda

aedtm

FROM pa0169

INTO TABLE oi_0169

for all entries in it_pa0001

WHERE pernr = it_pa0001-pernr

AND begda <= v_parm1date

AND endda >= v_parm1date

AND bplan IN r_bplan .

SORT oi_0169 BY pernr ASCENDING begda DESCENDING.

DELETE ADJACENT DUPLICATES FROM oi_0169 COMPARING pernr.

endif.

Regards,

Srini.

[Former Member]

This message was moderated.