database tables linking...

Former Member · ‎2008 Mar 10

hello everyone,

can anyone tell me how to link the tables

BKPF, BSEG , KNA1, VBRK.

I have to get data linking all these tables with each other.

starting from BKPF.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Mar 10, 2008 4:47 PM

Former Member · ‎2008 Mar 10

Hi,

Refer

http://www.geocities.com/sapcircle/Relations.pdf

Regards

Kiran Sure

Former Member · ‎2008 Mar 10

hi,

Here is the sample code:

REPORT ZSALES_REGISTER

LINE-SIZE 351

NO STANDARD PAGE HEADING

LINE-COUNT 65

MESSAGE-ID 00.

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

TABLES *

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

TABLES: VBRK, "Billing Document: Header Data

VBRP, "Billing Document: Item Data

BKPF, "Accounting document header

t685t, "Conditions: Types: Texts

KNA1,

KONV. "Conditions (Procedure Data)

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

INTERNAL TABLES AND STRUCTURES *

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

*internal table for item data

DATA: BEGIN OF IT_ITEM OCCURS 0,

BUDAT LIKE BKPF-BUDAT,

BELNR LIKE BKPF-BELNR,

POSNR LIKE VBRP-POSNR,

VBELN LIKE VBRK-VBELN,

WERKS LIKE VBRP-WERKS,

KUNAG LIKE VBRK-KUNAG,

KNUMV LIKE VBRK-KNUMV,

NAME1 LIKE KNA1-NAME1,

BASE_PRICE LIKE KONV-KWERT,

BAS_EXC_DUTY_RATE LIKE KONV-KBETR,

BAS_EXC_DUTY_AMT LIKE KONV-KWERT,

ECESS_RATE LIKE KONV-KBETR,

ECESS_AMT LIKE KONV-KWERT,

LST_VAT_RATE LIKE KONV-KBETR,

LST_VAT_AMT LIKE KONV-KWERT,

SUBCHARGE_RATE LIKE KONV-KBETR,

SUBCHARGE_AMT LIKE KONV-KWERT,

CST_RATE LIKE KONV-KBETR,

CST_AMT LIKE KONV-KWERT,

OTHERS LIKE KONV-KWERT,

V_VAL LIKE KONV-KWERT,

TOTAL LIKE KONV-KWERT,

END OF IT_ITEM.

*internal table to fetch customer name

DATA: BEGIN OF IT_KNA1 OCCURS 0,

NAME1 LIKE KNA1-NAME1,

END OF IT_KNA1.

*internal table for price data

DATA: BEGIN OF IT_KONV OCCURS 0,

KNUMV LIKE KONV-KNUMV,

KPOSN LIKE KONV-KPOSN,

KSCHL LIKE KONV-KSCHL,

KBETR LIKE KONV-KBETR,

KWERT LIKE KONV-KWERT,

END OF IT_KONV.

*Work area fro item internal table

*data wa_item like it_item.

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

*VARIABLES

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

DATA: V_VBELN LIKE VBUK-VBELN,

V_VAL LIKE KONV-KWERT,

TOTAL LIKE KONV-KWERT,

V_PR00 LIKE KONV-KWERT,

V_JEX2 LIKE KONV-KWERT,

V_JCES LIKE KONV-KWERT,

V_JIN2_JIN6 LIKE KONV-KWERT,

V_ZLSR LIKE KONV-KWERT,

V_JIN1 LIKE KONV-KWERT,

V_OTHERS LIKE KONV-KWERT,

V_KSCHL LIKE KONV-KSCHL,

V_TEMPVAL(15),

V_BASIC_PRICE LIKE KONV-KBETR,

V_REPID LIKE SY-REPID. "For report id

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

*SELECTION SCREEN DEFINITIONS

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

SELECTION-SCREEN BEGIN OF BLOCK B1.

SELECTION-SCREEN BEGIN OF BLOCK B WITH FRAME TITLE TEXT-000.

PARAMETERS: P_COMCOD LIKE BKPF-BUKRS." OBLIGATORY.

SELECT-OPTIONS: S_DATE FOR BKPF-BUDAT." OBLIGATORY.

SELECTION-SCREEN END OF BLOCK B .

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-001.

SELECTION-SCREEN BEGIN OF LINE.

PARAMETERS: P_EX1 RADIOBUTTON GROUP RAD1.

SELECTION-SCREEN COMMENT 5(50) TEXT-EX1.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN BEGIN OF LINE.

PARAMETERS: P_EX2 RADIOBUTTON GROUP RAD1 DEFAULT 'X'.

SELECTION-SCREEN COMMENT 5(50) TEXT-EX2.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP 1.

SELECTION-SCREEN END OF BLOCK B2 .

SELECTION-SCREEN END OF BLOCK B1.

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

INITIALIZATION

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

INITIALIZATION.

V_REPID = SY-REPID.

p_ex1 = 'X'.

IF P_EX1 = 'X'.

SELECTION-SCREEN BEGIN OF BLOCK B3 WITH FRAME TITLE TEXT-002.

PARAMETERS: P_PLANT LIKE VBRP-WERKS." OBLIGATORY.

SELECTION-SCREEN END OF BLOCK B3 .

ENDIF.

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

*START-OF-SELECTION

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

START-OF-SELECTION.

PERFORM GET_DATA.

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

*END-OF-SELECTION

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

IF NOT IT_ITEM[] IS INITIAL.

PERFORM DISPLAY_DATA.

ELSEif p_plant is initial.

if p_ex1 = 'X'.

MESSAGE S011(ZMSG).

else.

MESSAGE S012(ZMSG).

endif.

else.

MESSAGE S012(ZMSG).

ENDIF.

&----

*& Form GET_DATA

&----

text

-

form for get data

FORM GET_DATA.

*Getting BKPF-VBRK-VBRP details based on selection values

IF P_EX1 = 'X' .

SELECT A_{BUKRS A}BUDAT A_{BLART A}BELNR A_{AWTYP A}AWKEY

B_{POSNR B}WERKS B~VBELN

C_{KUNAG C}VBTYP C~KNUMV

INTO CORRESPONDING FIELDS OF TABLE IT_ITEM

FROM BKPF AS A

JOIN VBRP AS B

ON A_{AWKEY = B}VBELN

JOIN VBRK AS C

ON B_{VBELN = C}VBELN

WHERE A~BUKRS = P_COMCOD AND

A~BUDAT IN S_DATE AND

A~AWTYP = 'VBRK' AND

( A~BLART = 'DR' OR

A~BLART = 'DG' OR

A~BLART = 'DA' ) AND

B~WERKS = P_PLANT.

ELSEIF P_EX2 = 'X'.

SELECT A_{BUKRS A}BUDAT A_{BLART A}BELNR A_{AWTYP A}AWKEY

B_{POSNR B}WERKS B~VBELN

C_{KUNAG C}VBTYP C~KNUMV

INTO CORRESPONDING FIELDS OF TABLE IT_ITEM

FROM BKPF AS A

JOIN VBRP AS B

ON A_{AWKEY = B}VBELN

JOIN VBRK AS C

ON B_{VBELN = C}VBELN

WHERE A~BUKRS = P_COMCOD AND

A~BUDAT IN S_DATE AND

A~AWTYP = 'VBRK' AND

( A~BLART = 'DR' OR

A~BLART = 'DG' OR

A~BLART = 'DA' ).

ENDIF.

*Getting KNA1 details based on selection values

LOOP AT IT_ITEM.

SELECT SINGLE NAME1 FROM KNA1 INTO IT_KNA1

WHERE KUNNR = IT_ITEM-KUNAG.

APPEND IT_KNA1.

MOVE IT_KNA1-NAME1 TO IT_ITEM-NAME1.

MODIFY IT_ITEM.

CLEAR IT_ITEM.

CLEAR IT_KNA1.

ENDLOOP.

if not it_item[] is initial.

*Getting pricing for the seleted values

SELECT KNUMV KPOSN KSCHL KBETR KWERT

FROM KONV

INTO TABLE IT_KONV

FOR ALL ENTRIES IN IT_ITEM

WHERE KNUMV = IT_ITEM-KNUMV

AND KPOSN = IT_ITEM-POSNR.

LOOP AT IT_KONV.

IT_KONV-KBETR = IT_KONV-KBETR / 10.

MODIFY IT_KONV.

CLEAR IT_KONV.

ENDLOOP.

*

*Filling item table for rest

PERFORM GET_VALUE.

ENDFORM.

-

FORM DISPLAY_DATA *

-

FORM TO DISPLAY OUTPUT *

-

TOP-OF-PAGE.

WRITE: / 'The Sales Register from ', S_DATE-LOW, 'to', S_DATE-HIGH,

P_COMCOD, P_PLANT.

SKIP 2.

-

FORM DISPLAY_DATA *

-

........ *

-

FORM DISPLAY_DATA.

SORT IT_ITEM BY WERKS VBELN.

IF P_EX1 = 'X'.

WRITE: / 'posting date', 16 'billing doc', 30 'sold to party',

55 'Name', 87 'base price',

104 'basic exc rate', 122 'basic exc amt', 140 'ecess rate',

161 'ecess amt', 175 'lst/vat rate', 192 'lst/vat amt',

209 'surcharge rate', 227 'surcharge amt', 246 'cst rate',

266 'cst amt', 284 'OTHERS', 301 'sub total'.

WRITE: SY-ULINE.

LOOP AT IT_ITEM.

WRITE: / IT_ITEM-BUDAT, 15 SY-VLINE, 16 IT_ITEM-VBELN,

30 SY-VLINE, 31 IT_ITEM-KUNAG, 45 SY-VLINE,

46 IT_ITEM-NAME1, 80 SY-VLINE, 81 IT_ITEM-BASE_PRICE, 98 SY-VLINE,

99 IT_ITEM-BAS_EXC_DUTY_RATE, 115 SY-VLINE,

116 IT_ITEM-BAS_EXC_DUTY_AMT, 133 SY-VLINE,

134 IT_ITEM-ECESS_RATE, 150 SY-VLINE, 151 IT_ITEM-ECESS_AMT,

168 SY-VLINE, 169 IT_ITEM-LST_VAT_RATE, 185 SY-VLINE,

186 IT_ITEM-LST_VAT_AMT, 203 SY-VLINE,

204 IT_ITEM-SUBCHARGE_RATE, 221 SY-VLINE,

222 IT_ITEM-SUBCHARGE_AMT, 239 SY-VLINE, 240 IT_ITEM-CST_RATE,

256 SY-VLINE, 257 IT_ITEM-CST_AMT, 274 SY-VLINE,

275 IT_ITEM-OTHERS, 292 SY-VLINE,

293 IT_ITEM-V_VAL, 317 SY-VLINE, 318 IT_ITEM-WERKS,

325 SY-VLINE, 326 IT_ITEM-BELNR.

*write: sy-uline.

ENDLOOP.

WRITE: SY-ULINE.

SKIP 1.

WRITE: 81 V_PR00, 116 V_JEX2, 151 V_JCES, 186 V_JIN2_JIN6,

221 V_ZLSR, 256 V_JIN1, 273 V_OTHERS, 291 IT_ITEM-TOTAL.

ELSEIF P_EX2 = 'X'.

SORT IT_ITEM BY WERKS VBELN.

WRITE: / 'plant', 16 'billing doc', 35 'base price',

55 'basic exc rate', 75 'basic exc amt', 95 'ecess rate',

115 'ecess amt', 135 'lst/vat rate', 155 'lst/vat amt',

175 'surcharge rate', 195 'surcharge amt', 215 'cst rate',

235 'cst amt', 255 'OTHERS', 275 'sub total'.

WRITE: SY-ULINE.

LOOP AT IT_ITEM.

WRITE: / IT_ITEM-WERKS, 15 SY-VLINE, 16 IT_ITEM-VBELN,

35 SY-VLINE, 36 IT_ITEM-BASE_PRICE, 55 SY-VLINE,

56 IT_ITEM-BAS_EXC_DUTY_RATE, 75 SY-VLINE,

76 IT_ITEM-BAS_EXC_DUTY_AMT, 95 SY-VLINE,

96 IT_ITEM-ECESS_RATE, 115 SY-VLINE, 116 IT_ITEM-ECESS_AMT,

135 SY-VLINE, 136 IT_ITEM-LST_VAT_RATE, 155 SY-VLINE,

156 IT_ITEM-LST_VAT_AMT, 175 SY-VLINE,

176 IT_ITEM-SUBCHARGE_RATE, 195 SY-VLINE,

196 IT_ITEM-SUBCHARGE_AMT, 215 SY-VLINE, 216 IT_ITEM-CST_RATE,

235 SY-VLINE, 236 IT_ITEM-CST_AMT, 255 SY-VLINE,

256 IT_ITEM-OTHERS, 275 SY-VLINE,

276 IT_ITEM-V_VAL, 295 SY-VLINE.

*write: sy-uline.

ENDLOOP.

WRITE: SY-ULINE.

SKIP 1.

WRITE: 36 V_PR00, 76 V_JEX2, 116 V_JCES, 156 V_JIN2_JIN6,

196 V_ZLSR, 236 V_JIN1, 256 V_OTHERS, 276 IT_ITEM-TOTAL.

ENDIF.

ENDFORM. " DISPLAY_DATA

-

FORM GET_VALUE *

-

TO FETCH THE PRICE FROM IT_KNA1 DEPENDING UPON CONDITION TYPE *

-

FORM GET_VALUE.

*Getting BASE VALUE

*clear total.

LOOP AT IT_ITEM.

PERFORM GET_PRICE.

ENDLOOP.

ENDFORM.

-

FORM GET_PRICE *

-

........ *

-

FORM GET_PRICE.

CLEAR V_VAL.

LOOP AT IT_KONV WHERE KNUMV = IT_ITEM-KNUMV.

" and kposn = it_item-posnr.

V_KSCHL = IT_KONV-KSCHL.

CASE V_KSCHL.

*For basic value

WHEN 'PR00'.

IT_ITEM-BASE_PRICE = IT_ITEM-BASE_PRICE + IT_KONV-KWERT.