reg tables.

[Former Member]

Hi,

I have the requirement to join two tables and extract data.

BKPF and BSEG(CLUSTER TABLE).

Initially i have to select data from BKPF based on that i have to extract for all the records of BKPF from BSEG.

Since BSEG is a cluster table can anyone suggest me how best i can extract data from any secondary index tables.

Regards,

Ratna

[Former Member]

Hi Ratna,

Check this program,

i hope it is helpful to you.

TABLES : bkpf,

bseg.

************************************************************************

• INTERNAL TABLE AND WORK AREA FOR THE FIELDS IN BKPF TABLE *

************************************************************************

DATA : BEGIN OF itab_bkpf OCCURS 0,

bukrs LIKE bkpf-bukrs, "Company Code.

gjahr LIKE bkpf-gjahr, "Fiscal Year.

budat LIKE bkpf-budat, "Posting Date in the Document.

belnr LIKE bkpf-belnr, "Accounting document number.

blart LIKE bkpf-blart, "Document Type.

END OF itab_bkpf.

DATA : wa_bkpf LIKE LINE OF itab_bkpf.

************************************************************************

• INTERNAL TABLE AND WORK AREA FOR THE FIEDLS IN BSEG TABLE *

************************************************************************

DATA : BEGIN OF itab_bseg_debit OCCURS 0,

bukrs LIKE bseg-bukrs, "Company Code.

gjahr LIKE bseg-gjahr, "Fiscal Year.

belnr LIKE bseg-belnr, "Accounting Document Number.

buzei LIKE bseg-buzei, "Line Item.

hkont LIKE bseg-hkont, "General Leadger Account.

shkzg LIKE bseg-shkzg, "Credit/Debit Indicator.

wrbtr LIKE bseg-wrbtr, "Amount in Document Currency.

pswsl LIKE bseg-pswsl, "Update Currency for Gen.Ledger

dmbtr LIKE bseg-dmbtr, "Amount in local currency.

sgtxt LIKE bseg-sgtxt, "Item Text.

zuonr LIKE bseg-zuonr, "Assignment Number.

END OF itab_bseg_debit.

DATA : itab_bseg_credit LIKE STANDARD TABLE OF itab_bseg_debit WITH

HEADER LINE.

************************************************************************

• FINAL OUTPUT INTERNAL TABLE *

************************************************************************

DATA : BEGIN OF itab_output OCCURS 0,

belnr(08),

bukrs(04),

budat LIKE bkpf-budat,

buzei(03),

hkont(07),

blart(02),

shkzg(01),

wrbtr(08),

pswsl(05),

dmbtr(10),

sgtxt(19),

zuonr(10),

END OF itab_output.

************************************************************************

CONSTANTS : c_debit TYPE c VALUE 'S',

c_credit TYPE c VALUE 'H'.

************************************************************************

• SELECT-OPTIONS *

************************************************************************

SELECTION-SCREEN BEGIN OF BLOCK input WITH FRAME TITLE text-t01.

SELECT-OPTIONS : s_bukrs FOR bkpf-bukrs.

PARAMETERS : p_year LIKE bkpf-gjahr.

SELECT-OPTIONS : s_budat FOR bkpf-budat,

s_dbacct FOR bseg-hkont,

s_cracct FOR bseg-hkont,

s_amt FOR bseg-dmbtr.

SELECTION-SCREEN END OF BLOCK input.

************************************************************************

• SELECTING RECORDS FROM BKPF TABLE BASED ON THE CONDITION *

************************************************************************

SELECT bukrs gjahr budat belnr blart

FROM bkpf INTO TABLE itab_bkpf

WHERE bukrs IN s_bukrs AND

gjahr EQ p_year AND

budat IN s_budat.

************************************************************************

• SELECTING DEBIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *

• NUMBER SELECTED FROM BKPF *

************************************************************************

IF NOT itab_bkpf[] IS INITIAL.

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_debit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_dbacct AND

shkzg EQ c_debit AND

dmbtr IN s_amt.

************************************************************************

• SELECTING CREDIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *

• NUMBER SELECTED FROM BKPF *

************************************************************************

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_credit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_cracct AND

shkzg EQ c_credit AND

dmbtr IN s_amt.

ENDIF.

SORT itab_bkpf BY bukrs gjahr belnr.

SORT itab_bseg_credit BY bukrs gjahr belnr.

***********************************************************************

• LOOPING THE DEBIT ENTRIES

***********************************************************************

LOOP AT itab_bseg_debit.

***********************************************************************

• READING THE CREDIT ENTRIES WHICH MATCHES WITH HE CURRENT DOC. NUMBER

***********************************************************************

READ TABLE itab_bseg_credit WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

IF sy-subrc EQ 0.

***********************************************************************

*READING THE POSTING DATE AND DOCUMENT TYPE FOR THE CURRENT DOUCMENT

• AND APPENDING THE DEBIT AND CREDIT ENTRIES

***********************************************************************

READ TABLE itab_bkpf INTO wa_bkpf WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

itab_output-belnr = itab_bseg_debit-belnr.

itab_output-bukrs = itab_bseg_debit-bukrs.

itab_output-budat = wa_bkpf-budat.

itab_output-buzei = itab_bseg_debit-buzei.

itab_output-hkont = itab_bseg_debit-hkont.

itab_output-blart = wa_bkpf-blart.

itab_output-shkzg = itab_bseg_debit-shkzg.

itab_output-wrbtr = itab_bseg_debit-wrbtr.

itab_output-pswsl = itab_bseg_debit-pswsl.

itab_output-dmbtr = itab_bseg_debit-dmbtr.

itab_output-sgtxt = itab_bseg_debit-sgtxt.

itab_output-zuonr = itab_bseg_debit-zuonr.

APPEND itab_output.

itab_output-belnr = itab_bseg_credit-belnr.

itab_output-bukrs = itab_bseg_credit-bukrs.

itab_output-budat = wa_bkpf-budat.

itab_output-buzei = itab_bseg_credit-buzei.

itab_output-hkont = itab_bseg_credit-hkont.

itab_output-blart = wa_bkpf-blart.

itab_output-shkzg = itab_bseg_credit-shkzg.

itab_output-wrbtr = itab_bseg_credit-wrbtr.

itab_output-pswsl = itab_bseg_credit-pswsl.

itab_output-dmbtr = itab_bseg_credit-dmbtr.

itab_output-sgtxt = itab_bseg_credit-sgtxt.

itab_output-zuonr = itab_bseg_credit-zuonr.

APPEND itab_output.

ENDIF.

ENDLOOP.

SORT itab_output BY belnr budat shkzg.

***************************************************************************

<b>Reward if it is useful to you.</b>

[Former Member]

Hi Ratna,

You may use the statement

SELECT XXX, YYY

INTO TABLE IT_BSEG

FROM BSEG

FOR ALL ENTRIES IN TABLE BKPF

WHERE

BSEG-BUKRS = BKPF-BUKRS AND

BSEG-BELNR = BKPF-BELNR AND

BSEG-GJAHR = BKPF-GJAHR.

[Former Member]

Hi

SELECT  bkpf~lifnr bkpf~bukrs INTO    CORRESPONDING   FIELDS OF  TABLE int_outtab

  FROM (  bkpf

           JOIN bseg ON  bkpf~lifnr = bseg~lifnr )
  WHERE  bkpf~bukrs  =  P_bukrs.

Rewards if helpfull

Regards

Pavan

[Former Member]

Hi Ratna,

Use all key fields in where condition as per the order of BSEG table fields.

Some times more secondary index uses is may decrease the performance.

Regards,

Venkat

[Former Member]

Hi Ratna,

Check this program,

i hope it is helpful to you.

TABLES : bkpf,

bseg.

************************************************************************

• INTERNAL TABLE AND WORK AREA FOR THE FIELDS IN BKPF TABLE *

************************************************************************

DATA : BEGIN OF itab_bkpf OCCURS 0,

bukrs LIKE bkpf-bukrs, "Company Code.

gjahr LIKE bkpf-gjahr, "Fiscal Year.

budat LIKE bkpf-budat, "Posting Date in the Document.

belnr LIKE bkpf-belnr, "Accounting document number.

blart LIKE bkpf-blart, "Document Type.

END OF itab_bkpf.

DATA : wa_bkpf LIKE LINE OF itab_bkpf.

************************************************************************

• INTERNAL TABLE AND WORK AREA FOR THE FIEDLS IN BSEG TABLE *

************************************************************************

DATA : BEGIN OF itab_bseg_debit OCCURS 0,

bukrs LIKE bseg-bukrs, "Company Code.

gjahr LIKE bseg-gjahr, "Fiscal Year.

belnr LIKE bseg-belnr, "Accounting Document Number.

buzei LIKE bseg-buzei, "Line Item.

hkont LIKE bseg-hkont, "General Leadger Account.

shkzg LIKE bseg-shkzg, "Credit/Debit Indicator.

wrbtr LIKE bseg-wrbtr, "Amount in Document Currency.

pswsl LIKE bseg-pswsl, "Update Currency for Gen.Ledger

dmbtr LIKE bseg-dmbtr, "Amount in local currency.

sgtxt LIKE bseg-sgtxt, "Item Text.

zuonr LIKE bseg-zuonr, "Assignment Number.

END OF itab_bseg_debit.

DATA : itab_bseg_credit LIKE STANDARD TABLE OF itab_bseg_debit WITH

HEADER LINE.

************************************************************************

• FINAL OUTPUT INTERNAL TABLE *

************************************************************************

DATA : BEGIN OF itab_output OCCURS 0,

belnr(08),

bukrs(04),

budat LIKE bkpf-budat,

buzei(03),

hkont(07),

blart(02),

shkzg(01),

wrbtr(08),

pswsl(05),

dmbtr(10),

sgtxt(19),

zuonr(10),

END OF itab_output.

************************************************************************

CONSTANTS : c_debit TYPE c VALUE 'S',

c_credit TYPE c VALUE 'H'.

************************************************************************

• SELECT-OPTIONS *

************************************************************************

SELECTION-SCREEN BEGIN OF BLOCK input WITH FRAME TITLE text-t01.

SELECT-OPTIONS : s_bukrs FOR bkpf-bukrs.

PARAMETERS : p_year LIKE bkpf-gjahr.

SELECT-OPTIONS : s_budat FOR bkpf-budat,

s_dbacct FOR bseg-hkont,

s_cracct FOR bseg-hkont,

s_amt FOR bseg-dmbtr.

SELECTION-SCREEN END OF BLOCK input.

************************************************************************

• SELECTING RECORDS FROM BKPF TABLE BASED ON THE CONDITION *

************************************************************************

SELECT bukrs gjahr budat belnr blart

FROM bkpf INTO TABLE itab_bkpf

WHERE bukrs IN s_bukrs AND

gjahr EQ p_year AND

budat IN s_budat.

************************************************************************

• SELECTING DEBIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *

• NUMBER SELECTED FROM BKPF *

************************************************************************

IF NOT itab_bkpf[] IS INITIAL.

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_debit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_dbacct AND

shkzg EQ c_debit AND

dmbtr IN s_amt.

************************************************************************

• SELECTING CREDIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *

• NUMBER SELECTED FROM BKPF *

************************************************************************

SELECT bukrs gjahr belnr buzei

hkont shkzg wrbtr pswsl

dmbtr sgtxt zuonr

FROM bseg INTO TABLE itab_bseg_credit

FOR ALL ENTRIES IN itab_bkpf

WHERE bukrs EQ itab_bkpf-bukrs AND

belnr EQ itab_bkpf-belnr AND

gjahr EQ itab_bkpf-gjahr AND

hkont IN s_cracct AND

shkzg EQ c_credit AND

dmbtr IN s_amt.

ENDIF.

SORT itab_bkpf BY bukrs gjahr belnr.

SORT itab_bseg_credit BY bukrs gjahr belnr.

***********************************************************************

• LOOPING THE DEBIT ENTRIES

***********************************************************************

LOOP AT itab_bseg_debit.

***********************************************************************

• READING THE CREDIT ENTRIES WHICH MATCHES WITH HE CURRENT DOC. NUMBER

***********************************************************************

READ TABLE itab_bseg_credit WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

IF sy-subrc EQ 0.

***********************************************************************

*READING THE POSTING DATE AND DOCUMENT TYPE FOR THE CURRENT DOUCMENT

• AND APPENDING THE DEBIT AND CREDIT ENTRIES

***********************************************************************

READ TABLE itab_bkpf INTO wa_bkpf WITH KEY

bukrs = itab_bseg_debit-bukrs

gjahr = itab_bseg_debit-gjahr

belnr = itab_bseg_debit-belnr BINARY SEARCH.

itab_output-belnr = itab_bseg_debit-belnr.

itab_output-bukrs = itab_bseg_debit-bukrs.

itab_output-budat = wa_bkpf-budat.

itab_output-buzei = itab_bseg_debit-buzei.

itab_output-hkont = itab_bseg_debit-hkont.

itab_output-blart = wa_bkpf-blart.

itab_output-shkzg = itab_bseg_debit-shkzg.

itab_output-wrbtr = itab_bseg_debit-wrbtr.

itab_output-pswsl = itab_bseg_debit-pswsl.

itab_output-dmbtr = itab_bseg_debit-dmbtr.

itab_output-sgtxt = itab_bseg_debit-sgtxt.

itab_output-zuonr = itab_bseg_debit-zuonr.

APPEND itab_output.

itab_output-belnr = itab_bseg_credit-belnr.

itab_output-bukrs = itab_bseg_credit-bukrs.

itab_output-budat = wa_bkpf-budat.

itab_output-buzei = itab_bseg_credit-buzei.

itab_output-hkont = itab_bseg_credit-hkont.

itab_output-blart = wa_bkpf-blart.

itab_output-shkzg = itab_bseg_credit-shkzg.

itab_output-wrbtr = itab_bseg_credit-wrbtr.

itab_output-pswsl = itab_bseg_credit-pswsl.

itab_output-dmbtr = itab_bseg_credit-dmbtr.

itab_output-sgtxt = itab_bseg_credit-sgtxt.

itab_output-zuonr = itab_bseg_credit-zuonr.

APPEND itab_output.

ENDIF.

ENDLOOP.

SORT itab_output BY belnr budat shkzg.

***************************************************************************

<b>Reward if it is useful to you.</b>