vendor balances

Former Member · ‎01-07-2008

hi experts,

i have to calculate vendor cumulative balances

i.e., from today to last 15 months what was the balance of vendor i need to display (same as fk10n transaction)

select options are lifnr and bukrs from lfc1 table

please help me if any one has code or suggest me to proceed further

its urgent

please please help me.

andreas_mann3 · ‎01-07-2008

hi Hemal,

use:

- logical database KDF

- use statment like this:

SELECT SINGLE * from lfc1

WHERE LIFNR = itab-lifnr

AND BUKRS = itab-bukrs

AND GJAHR = gjahr.

check sy-subrc = 0.

clear : hsum, ssum.

ADD lfc1-UM01H THEN lfc1-UM02H UNTIL lfc1-UM16H GIVING hsum.

ADD lfc1-UM01s THEN lfc1-UM02s UNTIL lfc1-UM16s GIVING ssum.

check hsum ne 0 or ssum ne 0.

change that mimik (add...) with parameter "according to r_buper"

hope that helps

Andreas

Edited by: Andreas Mann on Jan 7, 2008 2:40 PM

Former Member · ‎01-07-2008

Hi da

Please refer following coding

&----

*& Report Z_FI_VENDOR_BALANCE *

*& *

&----

*& *

&----

REPORT z_fi_vendor_balance NO STANDARD PAGE HEADING

LINE-SIZE 114 LINE-COUNT 65(3) .

INCLUDE <icon>.

TABLES: lfa1,lfb1,lfc1,lfc3,bseg,bkpf,bsid,t001,skat,bsad,payr,BSIS,

GLT0,kna1.

*this table is of same structure as bsad too.

DATA: t_temp LIKE bsid OCCURS 0 WITH HEADER LINE.

DATA: t_output LIKE bsid OCCURS 0 WITH HEADER LINE.

DATA: t_bkpf LIKE bkpf OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF t_bseg OCCURS 0,

bseg LIKE bseg,

blart LIKE bkpf-blart,

budat LIKE bkpf-budat,

xblnr LIKE bkpf-xblnr,

END OF t_bseg.

hide

DATA: umsav LIKE lfc1-umsav,

HSLVT like GLT0-HSLVT,

tot_crdt LIKE bseg-dmbtr,

tot_dbt LIKE bseg-dmbtr,

grand_tot LIKE bseg-dmbtr,

grand_dbt LIKE bseg-dmbtr,

t_blart like bkpf-blart,

t_budat like bkpf-budat,

grand_crdt LIKE bseg-dmbtr,

prev_year LIKE bseg-gjahr,

iterations TYPE i VALUE 0,

col_flag TYPE i.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS : p_bukrs LIKE bsis-bukrs OBLIGATORY, "cocode

p_gjahr LIKE bsis-gjahr OBLIGATORY

DEFAULT sy-datum+0(4). "Year

SELECTION-SCREEN: END OF BLOCK b1.

SELECTION-SCREEN: BEGIN OF BLOCK b2 WITH FRAME TITLE text-002.

SELECT-OPTIONS : s_budat FOR bsid-budat OBLIGATORY,

s_blart FOR bsid-blart NO-DISPLAY,

S_hkont FOR bsis-hkont OBLIGATORY. "G/L

SELECTION-SCREEN: END OF BLOCK b2.

SELECTION-SCREEN: BEGIN OF BLOCK b3 WITH FRAME TITLE text-003.

*SELECT-OPTIONS : s_lifnr FOR lfa1-lifnr OBLIGATORY. "Vendor

SELECTION-SCREEN: END OF BLOCK b3.

SELECTION-SCREEN: BEGIN OF BLOCK b4 WITH FRAME TITLE text-004.

PARAMETERS : p_detail AS CHECKBOX DEFAULT 'X'.

SELECTION-SCREEN: END OF BLOCK b4.

INITIALIZATION.

col_flag = 0.

START-OF-SELECTION.

CLEAR bseg.

CLEAR umsav.

CLEAR tot_dbt.

CLEAR tot_crdt.

CLEAR grand_tot.

CLEAR grand_crdt.

CLEAR grand_dbt.

CLEAR iterations.

DATA year_beg LIKE sy-datum.

DATA curr_year LIKE bseg-gjahr.

CONCATENATE '01' '01' p_gjahr INTO year_beg.

prev_year = p_gjahr - 1.

*Get company code name

SELECT SINGLE butxt FROM t001 INTO t001-butxt WHERE

bukrs EQ p_bukrs.

*First select each vendor number in selection option.

*hide

SELECT lifnr name1 FROM lfa1 INTO (lfa1-lifnr,lfa1-name1)
WHERE lifnr IN s_lifnr.

DATA : BEGIN OF GL_ACC OCCURS 0,

RACCT LIKE GLT0-RACCT,

END OF GL_ACC.

*data : TMP_BSEG LIKE BSEG occurs 0 with header line.

data : begin of tmp_bseg occurs 0,

bukrs like bseg-bukrs,

belnr like bseg-belnr,

gjahr like bseg-gjahr,

hkont like bseg-hkont,

dmbtr like bseg-dmbtr,

shkzg like bseg-shkzg,

SGTXT like bseg-SGTXT,

end of tmp_bseg.

data : begin of tmp_bseg1 occurs 0,

bukrs like bseg-bukrs,

budat like bkpf-budat,

belnr like bseg-belnr,

gjahr like bseg-gjahr,

hkont like bseg-hkont,

dmbtr like bseg-dmbtr,

shkzg like bseg-shkzg,

SGTXT like bseg-SGTXT,

end of tmp_bseg1.

select RACCT from GLT0 INTO CORRESPONDING FIELDS OF TABLE

GL_ACC where

RACCT in S_HKONT and

RYEAR EQ P_GJAHR and

BUKRS eq p_bukrs.

DELETE ADJACENT DUPLICATES FROM GL_ACC.

SORT GL_ACC BY RACCT.

sort tmp_bseg by belnr hkont.

select SINGLE RACCT from GLT0 INTO (GLT0-RACCT) where

RACCT in S_HKONT and

RYEAR EQ P_GJAHR and

BUKRS eq p_bukrs.

SELECT * FROM bseg into table tmp_bseg WHERE bukrs EQ p_bukrs AND
gjahr EQ p_gjahr AND
HKONT IN S_HKONT.
performance tuning

SELECT BUKRS BELNR GJAHR HKONT DMBTR SHKZG SGTXT

INTO

(tmp_bseg-bukrs, tmp_bseg-belnr, tmp_bseg-Gjahr,

tmp_bseg-hkont, tmp_bseg-dmbtr, tmp_bseg-shkzg,

tmp_bseg-SGTXT)

from bseg

where bukrs eq p_bukrs and gjahr eq p_gjahr and

hkont in s_hkont.

append tmp_bseg.

endselect.

sort tmp_bseg by hkont belnr.

LOOP AT GL_ACC .

HIDE
SELECT SINGLE akont FROM lfb1 INTO lfb1-akont WHERE
bukrs EQ p_bukrs AND lifnr EQ lfa1-lifnr.

new-page.

SELECT * FROM bseg into table tmp_bseg WHERE bukrs EQ p_bukrs AND
gjahr EQ p_gjahr AND
HKONT EQ GL_ACC-RACCT.

*

iterations = iterations + 1.

*Detailed report for each ogf the item

CLEAR: umsav,

tot_dbt,

tot_crdt,

lfc1,

lfc3,

bkpf,

bseg,

t_bkpf,

t_bseg.

REFRESH: t_bseg,

t_bkpf.

SELECT SINGLE * FROM GLT0 WHERE bukrs EQ p_bukrs AND

RYEAR EQ p_gjahr AND

RACCT EQ GL_ACC-RACCT.

SELECT SUM( hslvt ) INTO hslvt FROM glt0 WHERE rldnr EQ '00' AND

racct = GL_ACC-RACCT AND

rrcty EQ '0' AND

rvers EQ '001' AND

bukrs EQ p_bukrs AND

ryear EQ p_gjahr.

*loop at tmp_bseg.

SELECT * FROM bkpf WHERE gjahr EQ p_gjahr and
budat LT s_budat-low
and BUKRS eq p_bukrs
and BELNR eq tmp_bseg-belnr.

*

*if sy-subrc = 0.

IF sy-subrc = 0.
IF tmp_bseg-shkzg = 'S'.
HSLVT = HSLVT + tmp_BSEG-dmbtr.
ELSE.
HSLVT = HSLVT - tmp_bseg-dmbtr.
ENDIF.
ENDIF.

*endif.

SELECT * FROM bseg WHERE belnr EQ bkpf-belnr AND
bukrs EQ p_bukrs AND
gjahr EQ p_gjahr AND

HIDE lifnr EQ lfa1-lifnr AND

HKONT EQ GL_ACC-RACCT.

hkont EQ lfb1-akont.

IF sy-subrc = 0.
IF bseg-shkzg = 'S'.

hide
umsav = umsav + bseg-dmbtr.

HSLVT = HSLVT + BSEG-dmbtr.
ELSE.

hide

HSLVT = HSLVT - bseg-dmbtr.

umsav = umsav - bseg-dmbtr.

ENDIF.
ENDIF.
ENDSELECT.

ENDSELECT.

*endloop.

SELECT SINGLE txt50 INTO skat-txt50 FROM skat

WHERE spras EQ 'EN' AND

ktopl EQ 'ABC' AND

saknr EQ GL_ACC-RACCT.

WRITE: / sy-vline, (3) icon_customer AS ICON,

(50) lfa1-name1,

25(10) GLT0-RACCT, 40(50) skat-txt50.

WRITE: / sy-vline,'Opening Balance ',

95 sy-vline, (16) HSLVT,

sy-vline.

ULINE (114).

select * from bkpf into table t_bkpf where

bukrs eq p_bukrs and

gjahr eq p_gjahr and

budat in s_budat.

sort t_bkpf by budat.

clear : tmp_bseg.

loop at tmp_bseg where hkont eq gl_acc-racct.

loop at t_bkpf where belnr eq tmp_bseg-belnr.

IF sy-subrc eq 0.

tmp_bseg1-bukrs = tmp_bseg-bukrs.

tmp_bseg1-belnr = tmp_bseg-belnr.

tmp_bseg1-gjahr = tmp_bseg-gjahr.

tmp_bseg1-hkont = tmp_bseg-hkont.

tmp_bseg1-dmbtr = tmp_bseg-dmbtr.

tmp_bseg1-shkzg = tmp_bseg-shkzg.

tmp_bseg1-SGTXT = tmp_bseg-SGTXT.

tmp_bseg1-budat = t_bkpf-budat.

append tmp_bseg1.

else.

delete tmp_bseg where belnr = t_bkpf-belnr.
clear tmp_bseg.

endif.

endloop.

*DELETE ADJACENT DUPLICATES FROM tmp_bseg1.

sort tmp_bseg1 by budat belnr.

clear : tmp_bseg1.

LOOP AT TMP_BSEG1 where hkont eq GL_ACC-RACCT.

IF p_detail NE 'X'.

PERFORM col_change CHANGING col_flag.

ELSE.

ENDIF.

select single blart budat from bkpf into (t_blart, t_budat) where

belnr eq tmp_bseg1-belnr and

bukrs eq tmp_bseg1-bukrs and

gjahr eq tmp_bseg1-gjahr.

WRITE: / sy-vline,(10) T_budat,

' ', (10) tMP_bseg1-belnr,

' ',(2) T_blart, ' '.

IF tMP_bseg1-shkzg EQ 'H'. "Credit

HSLVT = HSLVT - tMP_bseg1-dmbtr.

tot_crdt = tot_crdt + tMP_bseg1-dmbtr.

WRITE: ' ',

(16) tMP_bseg1-dmbtr.

ELSE. "Debit

HSLVT = HSLVT + tMP_bseg1-dmbtr.

tot_dbt = tot_dbt + tMP_bseg1-dmbtr.

WRITE: (16) tMP_BSEG1-dmbtr,

' '.

ENDIF.

WRITE : (16) HSLVT,

sy-vline.

HIDE : t_bseg-bseg-belnr,

t_bseg-bseg-bukrs,

t_bseg-bseg-gjahr.

data : tbukrs like bseg-bukrs,

tbelnr like bseg-belnr,

tgjahr like bseg-gjahr,

thkont like bseg-hkont,

tdmbtr like bseg-dmbtr,

tshkzg like bseg-shkzg,

tSGTXT like bseg-SGTXT,

tLIFNR like bseg-lifnr,

tsaknr like bseg-saknr,

tkunnr like bseg-kunnr.

IF p_detail EQ 'X'.

SELECT bukrs gjahr belnr hkont dmbtr shkzg sgtxt lifnr SAKNR kunnr

into

(tbukrs,tgjahr,tbelnr,thkont,tdmbtr,tshkzg,tsgtxt,tlifnr, tsaknr,tkunnr)

FROM bseg WHERE bukrs EQ tMP_bseg1-bukrs AND

gjahr EQ tMP_bseg1-gjahr AND

belnr EQ tMP_bseg1-belnr AND

hkont NE TMP_BSEG1-hkont.

WRITE: / sy-vline.

DATA : GLNAME LIKE SKAT-TXT50.

tmp hide

if tsaknr eq space or tlifnr eq space.

SELECT SINGLE txt20 INTO skat-txt20 FROM skat

WHERE spras EQ 'EN' AND

ktopl EQ 'ABC' AND

saknr EQ tHKONT.

WRITE: 17(6) THKONT, skat-txt20.

write : tsgtxt.

elseif tsaknr ne space or tlifnr ne space.

SELECT SINGLE name1 INTO lfa1-name1 FROM lfa1 WHERE

SPRAS eq 'E' AND lifnr EQ tlifnr

AND CONFS EQ SPACE.

write: 17(27) lfa1-name1.

clear : lfa1-name1.

ENDIF.

*

IF tkunnr NE space.
SELECT SINGLE name1 INTO kna1-name1 FROM kna1 WHERE
kunnr EQ tkunnr.
WRITE: 17(10) tkunnr, (16) kna1-name1.
ELSEIF tlifnr NE space.

*

SELECT SINGLE txt20 INTO skat-txt20 FROM skat
WHERE spras EQ 'EN' AND
ktopl EQ 'ABC' AND
saknr EQ tHKONT.
WRITE: 17(6) THKONT, skat-txt20.

*

SELECT SINGLE name1 INTO lfa1-name1 FROM lfa1 WHERE
SPRAS eq 'E' AND lifnr EQ tlifnr
AND CONFS EQ SPACE.
write: 17(27) lfa1-name1.

*

ELSEIF thkont NE space .
SELECT SINGLE txt20 INTO skat-txt20 FROM skat
WHERE spras EQ 'EN' AND
ktopl EQ 'ABC' AND
saknr EQ thkont.
WRITE: 17(6) thkont, skat-txt20 .

*

ENDIF.

IF tshkzg EQ 'H'.

WRITE: 'Cr', (14) tdmbtr.

ELSE.

WRITE: 'Dr', (14) tdmbtr.

ENDIF.

WRITE: 114 sy-vline.

HIDE : t_bseg-bseg-belnr,

t_bseg-bseg-bukrs,

t_bseg-bseg-gjahr.

IF tsgtxt NE space.

WRITE : / sy-vline, 17 tsgtxt,

114 sy-vline.

ENDIF.

HIDE : t_bseg-bseg-belnr,

t_bseg-bseg-bukrs,

t_bseg-bseg-gjahr.

clear : tbukrs,

tbelnr,

tgjahr,

thkont,

tdmbtr,

tshkzg,

tSGTXT.

ENDSELECT.

SELECT * FROM bseg WHERE bukrs EQ tMP_bseg-bukrs AND
gjahr EQ tMP_bseg-gjahr AND
belnr EQ tMP_bseg-belnr
AND hkont NE tMP_bseg-hkont.

*

WRITE: / sy-vline.

*

DATA : GLNAME LIKE SKAT-TXT50.

*

if BSEG-hkont ne space.
SELECT SINGLE txt20 INTO skat-txt20 FROM skat
WHERE spras EQ 'EN' AND
ktopl EQ 'ABC' AND
saknr EQ BSEG-hkont.
WRITE: 17(6) BSEG-hkont, skat-txt20.
ENDIF.

*

IF BSEG-shkzg EQ 'H'.
WRITE: 'Cr',(14) BSEG-dmbtr.
ELSE.
WRITE: 'Dr',(14) BSEG-dmbtr.
ENDIF.
WRITE: 114 sy-vline.

*

HIDE : t_bseg-bseg-belnr,
t_bseg-bseg-bukrs,
t_bseg-bseg-gjahr.

*

IF BSEG-sgtxt NE space.
WRITE : / sy-vline, 17 BSEG-sgtxt,
114 sy-vline.
ENDIF.

*

HIDE : t_bseg-bseg-belnr,
t_bseg-bseg-bukrs,
t_bseg-bseg-gjahr.
ENDSELECT.

ENDIF.

ENDLOOP.

grand_tot = grand_tot + HSLVT.

grand_crdt = grand_crdt + tot_crdt.

grand_dbt = grand_dbt + tot_dbt.

ULINE (114).

WRITE: / sy-vline, icon_sum AS ICON,

21(12) 'Total ',

39(21) ' ',

sy-vline, (14) tot_dbt,

sy-vline, (14) tot_crdt,

sy-vline, (16) HSLVT,

sy-vline.

ULINE (114).

endloop.

END-OF-SELECTION.

DATA l TYPE i VALUE 0.

CLEAR bsid.

IF iterations GT 1.

ULINE (114).

WRITE: / sy-vline, icon_sum AS ICON,

21(12) 'Grand Total',

39(21) ' ',

sy-vline, (14) grand_dbt,

sy-vline, (14) grand_crdt,

sy-vline, (16) grand_tot,

sy-vline.

ULINE (114).

ENDIF.

CLEAR t_bseg.

TOP-OF-PAGE.

format color col_key intensified on.

SKIP.

IF p_detail EQ 'X'.

ULINE (114).

WRITE: / sy-vline, 114 sy-vline.

NEW-LINE NO-SCROLLING.

WRITE: / sy-vline,44 t001-butxt CENTERED ,114 sy-vline.

WRITE: / sy-vline,'GL ACCOUNT BALANCE' UNDER t001-butxt,

114 sy-vline.

WRITE: / sy-vline,

'Run Date',(12) sy-datum,(54) ' ',

'Period', s_budat-low, 'to',

s_budat-high,114 sy-vline.

ULINE (114).

WRITE: / sy-vline, (11) ' Posting',

sy-vline, (18) ' Document Number ',

(20) ' Document Type',

' ',sy-vline,(14) ' Debit ',

sy-vline, (14) ' Credit',

sy-vline, (16) 'Cumul Balance',

sy-vline.

WRITE: / sy-vline, (11) ' Date',

sy-vline, (5) ' ',

(8) ' ',

'Particulars ',

sy-vline,(14) ' Amount (Rs)',

sy-vline, (14) ' Amount (Rs)',

sy-vline, (16) ' Amount (Rs) ',

sy-vline.

ELSE.

ULINE (114).

WRITE: / sy-vline, 114 sy-vline.

NEW-LINE NO-SCROLLING.

WRITE: / sy-vline,47 t001-butxt CENTERED,114 sy-vline.

WRITE: / sy-vline,'GL ACCOUNT BALANCE' UNDER t001-butxt,

114 sy-vline.

WRITE: / sy-vline,'Run Date',

(12) sy-datum,(54) ' ', 'Period', s_budat-low, 'to',

s_budat-high,114 sy-vline.

ULINE (114).

WRITE: / sy-vline, (11) ' Posting',

sy-vline, (12) ' Document ',

sy-vline, (4) 'Doc',

sy-vline, (16) ' Header ',

sy-vline, (16) ' Debit ',

sy-vline, (16) ' Credit',

sy-vline, (17) 'Cumul Balance',

sy-vline.

WRITE: / sy-vline, (11) ' Date',

sy-vline, (12) ' Number ',

sy-vline, (4) 'Type',

sy-vline, (16) ' Reference ',

sy-vline, (16) ' Amount (Rs)',

sy-vline, (17) ' Amount (Rs) ',

114 sy-vline.

ENDIF.

ULINE (114).

WRITE: / sy-vline, (3) icon_customer AS ICON,

25(10) GLT0-RACCT, 40(50) skat-txt50,

114 sy-vline.

uline (114).

END-OF-PAGE.

format color col_key intensified on.

ULINE (114).

WRITE: / sy-vline, 50 'Page', sy-pagno,114 sy-vline.

ULINE (114).

AT LINE-SELECTION.

IF t_bseg-bseg-belnr NE space.

SET PARAMETER ID 'BLN' FIELD t_bseg-bseg-belnr.

SET PARAMETER ID 'BUK' FIELD t_bseg-bseg-bukrs.

SET PARAMETER ID 'GJR' FIELD t_bseg-bseg-gjahr.

CLEAR t_bseg.

CALL TRANSACTION 'FB03' AND SKIP FIRST SCREEN.

ENDIF.

&----

*& Form col_change

&----

text

----

<--P_COL_FLAG text

----

FORM col_change CHANGING p_col_flag.

IF p_col_flag = 0.

format color col_normal intensified off.

p_col_flag = 1.

ELSE.

p_col_flag = 0.

format color col_normal intensified on.

ENDIF.

ENDFORM. "col_change