Link between SD invoice item and corresponding accounting document item

[Former Member]

Hi,

I have a problem regarding the link between the items of an SD invoice ( or

credit note ) and those of the corresponding accounting document. There's a

custom report which extracts information about each credit/debit note

produced within a certain period. This program has recently failed due to a

particular case: a credit note was generated starting by two note requests;

in other terms, cardinality: 1:N exists between the credit note and the

request document. Currently, the program generates duplicated records in

this case, because of the following schema:

-> main loop on VBAK data ( request document header data )

-> inner loop on BSEG data ( accounting document items );

our current access criteria in reading tb_bseg is only:

... where tb_bseg-belnr = tb_vbrk-vbeln.

Providing that tb_vbrk-vbeln doesn't differ and that both tb_vbak and tb_bseg

contain two records ( the two requests the first and the two accounting

document items the second ), the program fails as follows:

° first request -> two lines

° second request -> two lines.

Is there any way to link each invoice item with the corresponding credit

note item?

Thanks in advance to all of you.

Adrian.

[Former Member · Accepted solution]

Logic:

Table Joint: VBRK-XBLNR = BKPF-XBLNR

Accounting Document Number: BKPF-BELNR

Regards,

Rajesh Banka

Reward points if helpful.

[Former Member]

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

Topic: Free ABAP eBook Download

SAP MM, SD, FI, PS, PP, PM, HR, System Tables

Regards,

Rajesh Banka

[Former Member]

Dear Adrian,

I dont think it's possible that VBAK will have two records with same document number and client ID.

If you are dealing with different client ID's:

Did you try using 'AT NEW' statement in your program. The inner loop will be executed only once in this case. Hence, the duplicate records are not generated even if you two records in ABAK.

-> main loop on VBAK data ( request document header data )

at new vbeln.

-> inner loop on BSEG data ( accounting document items );

endloop. "BSEG

endat.

endloop. "VBAK

Reward points if this is helpful.

Regards,

Naveen.

[Former Member]

Dear Naveen,

maybe there has been a misunderstanding: tb_vbak, I wrote, contains two different

document numbers ( the two requests ); theoretically, it's correct that the program

loops on tb_vbak twice. The problem is that in my case the program isn't able

to access to tb_bseg ( for each request ) with different criteria ( other than merely

tb_vbrk-vbeln - the SD invoice number - ).

Thank you very much for your reply.

Adrian.

[Former Member]

Dear Adrian,

Fetch Accounting Document Number for Invoice and then Accounting Document Transaction Data.

Select belnr

from bkpf

into table it_bkpf

for all entries in it_vbrk

where xblnr = it_vbrk-xblnr.

Select (fields)

from bseg

into it_bseg

for all entries in it_bkpf

where belnr = it_bkpf-belnr.

Reward points if this is helpful.

Regards,

Naveen.

[Former Member]

Dear Adrian,

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.

V_VAL = V_VAL + IT_KONV-KWERT.

V_PR00 = V_PR00 + IT_KONV-KWERT.

WHEN 'JEX2'.

MOVE IT_KONV-KBETR TO IT_ITEM-BAS_EXC_DUTY_RATE.

IT_ITEM-BAS_EXC_DUTY_AMT = IT_ITEM-BAS_EXC_DUTY_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_JEX2 = V_JEX2 + IT_KONV-KWERT.

WHEN 'JCES'.

IT_ITEM-ECESS_AMT = IT_ITEM-ECESS_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_JCES = V_JCES + IT_KONV-KWERT.

WHEN 'JIN2'.

IT_ITEM-LST_VAT_AMT = IT_ITEM-LST_VAT_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_JIN2_JIN6 = V_JIN2_JIN6 + IT_KONV-KWERT.

WHEN 'JIN6'.

IT_ITEM-LST_VAT_AMT = IT_ITEM-LST_VAT_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_JIN2_JIN6 = V_JIN2_JIN6 + IT_KONV-KWERT.

WHEN 'ZLSR'.

IT_ITEM-SUBCHARGE_AMT = IT_ITEM-SUBCHARGE_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_ZLSR = V_ZLSR + IT_KONV-KWERT.

WHEN 'JIN1'.

MOVE IT_KONV-KBETR TO IT_ITEM-CST_RATE.

IT_ITEM-CST_AMT = IT_ITEM-CST_AMT + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_JIN1 = V_JIN1 + IT_KONV-KWERT.

WHEN OTHERS.

IT_ITEM-OTHERS = IT_ITEM-OTHERS + IT_KONV-KWERT.

V_VAL = V_VAL + IT_KONV-KWERT.

V_OTHERS = V_OTHERS + IT_KONV-KWERT.

ENDCASE.

ENDLOOP.

TOTAL = TOTAL + V_VAL.

MOVE V_VAL TO IT_ITEM-V_VAL.

MOVE TOTAL TO IT_ITEM-TOTAL.

• v_pr00 = v_pr00 + it_item-base_price.

MODIFY IT_ITEM.

CLEAR IT_ITEM.

ENDFORM. " GET_PRICE

Reward points if this is helpful.

Regards,

Naveen.

[Former Member]

Dear Adrian,

Go though the following link for performance tuning of your program (as you are using BSEG):

/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops

visit the following link for 'BAPI_ACC_INVOICE_RECEIPT_POST':

http://www.sapfans.com/sapfans/forum/intface/messages/4202.html

Hope this is helpful.

Regards,

Naveen.