topic Re: Regarding logical data base tables in Application Development and Automation Discussions

Regarding logical data base tables

Former Member — Wed, 21 Jun 2006 02:01:32 GMT

i am working on abap-hr. i want to know how to retrieve data from two different hr-database tables to two different internal tables and then send it to final internal table.can any one plz give me sample hr code.

your help is rewarded with points

bye

rad

Re: Regarding logical data base tables

RichHeilman — Wed, 21 Jun 2006 02:09:47 GMT

Just select the data from each db table into a corresponding internal table, then loop at the first one move the fields that you want to the 3rd internal table structure, then loop at the other internal table where the keys match and move the other data, then APPEND.




report zrich_0001.


data: ipa0001 type table of pa0001 with header line.
data: ipa0002 type table of pa0002 with header line.

data: begin of ipa occurs 0,
      pernr type pa0001-pernr,
      persg type pa0001-persg,
      nachn type pa0002-nachn,
      vorna type pa0002-vorna,
      end of ipa.

select * into table ipa0001 from pa0001.
select * into table ipa0002 from pa0002.


loop at ipa0001.
  clear ipa.
  ipa-pernr = ipa0001.
  ipa-persg = ipa0001.

  loop at ipa0002 where pernr = ipa0001-pernr.
    ipa-nachn = ipa0002-nachn.
    ipa-vorna = ipa0002-vorna.
    append ipa.
  endloop.
endloop.

loop at ipa.
  write:/ ipa.
endloop.

Of course this could also be done with an INNER JOIN

Regards,

Rich Heilman

Oops. Totally missed that you were referring to LDBs.

Message was edited by: Rich Heilman

Re: Regarding logical data base tables

Former Member — Wed, 21 Jun 2006 02:20:06 GMT

If you are using logical databases, then you don't have to give any SELECT statement.

1. For getting the personnel number, you use PNP logical database.

2. Declare infotypes of the 2 tables from which you need to fetch data, say for 0000, 0001.

3. Use the event GET PERNR in START-OF-SELECTION.

when you do this, an internal table will be created and will be populated with all pernr for the declared infotypes in P0000, P0001.

4. at the event END-OF-SELECTION, GET PERNR will be triggered and next pernr will be fetched and the internal tables with that pernr will be populated.

You can use these internal tables for getting the desired records by using the macros,

RP-PROVIDE-FROM-LAST ......

SAMPLE CODE:

infotypes: 0000, 0001, 0002.

tables: pa0000, pa0001, PERNR.

data: begin of itab occurs 0,

.....

end of itab.

Start-of-selection

START-OF-SELECTION.

GET PERNR.

RP-PROVIDE-FROM-LAST P0001 SPACE PN-BEGDA PN-ENDDA.

if no record is found then exit

IF PNP-SW-FOUND = 1.

ITAB-PERNR = PERNR-PERNR. "Emp no

ITAB-BUKRS = P0001-BUKRS. "Company code

ITAB-WERKS = P0001-WERKS. "Personnel area

ITAB-BTRTL = P0001-BTRTL. "Personnel sub-area

RP-PROVIDE-FROM-LAST P0002 SPACE PN-BEGDA PN-ENDDA.

if pnp-sw-found = 1.

ITAB-VORNA = P0002-VORNA.

ITAB-NACHN = P0002-NACHN.

endif.

ELSE.

EXIT.

ENDIF.

append itab.

END-OF-SELECTION.

this will fetch the last record. if you need to fetch all records then you can loop P0001 and get the values for that particular pernr.

loop at P0001.

itab-pernr = pernr-pernr.

itab-werks = P0001-werks.

.......

endloop.

Regards,

Navneeth

PS: If not clear, please let me know.

Message was edited by: Navneeth Saraogi

Re: Regarding logical data base tables

Former Member — Wed, 21 Jun 2006 02:20:41 GMT

Hi,

I have Used PNP logical database and for each PERNR Select name of the employee.Please look at the following sample code.

tables : pernr.

data : first_name like pa0002-vorna,

last_name like pa0002-nachn,

full_name(50),

get pernr.

********FULL NAME OF EMPLOYEE*************

*****FIRST NAME

select single vorna

from pa0002

into first_name

where pernr = pernr-pernr.

*****LAST NAME

select single nachn

from pa0002

into last_name

where pernr = pernr-pernr.

****FULL NAME

concatenate first_name '' last_name into full_name.

it_final-empnam = full_name.

append it_final.

Thanks,

Pramod

Re: Regarding logical data base tables

Former Member — Wed, 21 Jun 2006 02:47:43 GMT

Hi,

There is another example where i'have used BRM LDB and used GET events on header and Item tables.The following code pulls the record for CD doc. type and other doc type incase profit center is blank in BSEG table.

TABLES : bkpf,bseg,glpca.

SELECT-OPTIONS:s_glacc FOR bseg-hkont.

START-OF-SELECTION.

GET bkpf.

GET bseg.

CHECK bseg-prctr = space .

CHECK bseg-hkont IN s_glacc.

IF bkpf-blart = 'CD'.

w_refdocnr = bkpf-awkey(10).

w_aworg = bkpf-awkey+10(10).

SELECT SINGLE * FROM glpca

INTO wa_glpca

WHERE ryear = bseg-gjahr

AND refdocnr = w_refdocnr

AND aworg = w_aworg

AND refdocln = bseg-buzei

AND rbukrs = bkpf-bukrs.

ELSE.

SELECT SINGLE * FROM glpca

INTO wa_glpca

WHERE ryear = bseg-gjahr

AND refdocnr = bseg-belnr

AND refdocln = bseg-buzei

AND rbukrs = bkpf-bukrs.

Endif.

Hope this code will give you direction to proceed further.

Thanks,

Pramod

Re: Regarding logical data base tables

Former Member — Wed, 21 Jun 2006 03:39:38 GMT

GOOD

HEREWITH I AM GIVING SOME LINKS OF SAMPLE HR REPORT, GO THROUGH THEM I HOPE YOU WILL GET SOME IDEA FOR YOUR PROBLEM.

http://www.sap-img.com/hr021.htm

http://sap.ionelburlacu.ro/abap/sap2/SAP_Reports.html

http://www.sap-img.com/abap/sample-hr-reports-allocate-petrol-allowance.htm

THANKS

MRUTYUN