Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

repots( closing balance of a vendor)

Former Member
0 Kudos

hi.

i want to show the closing balnace of a vendor. in my report opening balance is coming. but if today i am displaying the report then it should be display the vendor's yesterday closing balance .

means at which date i will display the report the previous date colsing balance should be display.

pls give me some details.

my code is.

REPORT ZTEST12 NO STANDARD PAGE HEADING LINE-SIZE 191

LINE-COUNT 65

MESSAGE-ID zfimsg.

*-- table declarations

TABLES: bkpf,

bseg,

t001,

lfa1,

t003t,

payr.

*-- internal table declarations

DATA:BEGIN OF i_lfa1 OCCURS 0,

lifnr LIKE lfa1-lifnr,

name1 LIKE lfa1-name1,

opbal LIKE bseg-dmbtr,

END OF i_lfa1.

DATA:BEGIN OF i_bkpf OCCURS 0,

belnr LIKE bkpf-belnr,

blart LIKE bkpf-blart,

gjahr LIKE bkpf-gjahr,

budat LIKE bkpf-budat,

bldat LIKE bkpf-bldat,

xblnr LIKE bkpf-xblnr,

END OF i_bkpf.

DATA:BEGIN OF i_bseg OCCURS 0,

belnr LIKE bseg-belnr, " Document no

koart LIKE bseg-koart, " Account type

shkzg LIKE bseg-shkzg, " Debit/Credit Indicator

ktosl LIKE bseg-ktosl, " Transaction

hkont LIKE bseg-hkont, " GL Account

sgtxt LIKE bseg-sgtxt, " Item Text

lifnr like bseg-lifnr,

dmbtr like bseg-dmbtr,

bupla like bseg-bupla,

END OF i_bseg.

DATA:BEGIN OF i_t003t OCCURS 0,

spras LIKE t003t-spras,

blart LIKE t003t-blart, " Document Type

ltext LIKE t003t-ltext, " Doc Type Desc

END OF i_t003t,

BEGIN OF i_skat OCCURS 0,

saknr LIKE skat-saknr, " gl account

txt20 LIKE skat-txt20, " gl account text

END OF i_skat,

BEGIN OF i_payr OCCURS 0,

zbukr like payr-zbukr,

vblnr LIKE payr-vblnr,

chect LIKE payr-chect,

laufd LIKE payr-laufd,

END OF i_payr,

BEGIN OF itab OCCURS 0,

bukrs LIKE bkpf-bukrs,

gjahr LIKE bkpf-gjahr,

lifnr LIKE lfa1-lifnr,

budat LIKE bkpf-budat,

name1 LIKE lfa1-name1,

opbal LIKE bseg-dmbtr,

belnr LIKE bkpf-belnr,

blart LIKE bkpf-blart,

debit LIKE bseg-dmbtr,

credit LIKE bseg-dmbtr,

sgtxt LIKE bseg-sgtxt,

bldat LIKE bkpf-bldat,

xblnr LIKE bkpf-xblnr,

wt_qbshh LIKE with_item-wt_qbshh,

ltext LIKE t003t-ltext,

chect LIKE payr-chect,

laufd LIKE payr-laufd,

txt20 like skat-txt20,

saknr like skat-saknr,

balance like bseg-dmbtr,

hkont like bseg-hkont,

bupla like bseg-bupla,

END OF itab.

DATA:BEGIN OF i_witem OCCURS 0,

belnr TYPE with_item-belnr,

buzei TYPE with_item-buzei,

wt_qbshh TYPE with_item-wt_qbshh,

END OF i_witem.

DATA:itab_key_bal LIKE bapi3007_3 OCCURS 0 WITH HEADER LINE.

DATA:ibal LIKE itab_key_bal OCCURS 0 WITH HEADER LINE.

DATA : v_butxt LIKE t001-butxt.

*-- work variables

DATA: w_gjahr1 LIKE bkpf-gjahr,

w_gjahr2 LIKE bkpf-gjahr, "#EC *

w_pdate LIKE bkpf-budat,

w_name1 LIKE lfa1-name1,

w_opbal LIKE bseg-dmbtr,

w_cbal LIKE bseg-dmbtr,

w_cfbal LIKE bseg-dmbtr,

w_sbal LIKE bseg-dmbtr,

w_ftotal LIKE bseg-dmbtr,

w_cftotal LIKE bseg-dmbtr,

w_ctot LIKE bseg-dmbtr,

w_dtot LIKE bseg-dmbtr,

balance like bseg-dmbtr,

total like bseg-dmbtr.

*-- selection screen

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE tit.

PARAMETERS: p_bukrs LIKE bkpf-bukrs OBLIGATORY.

SELECT-OPTIONS: s_lifnr FOR lfa1-lifnr OBLIGATORY,

s_belnr FOR bkpf-belnr.

*----


CHANGE CODE -


SELECT-OPTIONS : S_PRCTR FOR BSEG-PRCTR,

S_BUPLA FOR BSEG-BUPLA.

*--


END CHANGE--


parameters: p_gjahr like bkpf-gjahr OBLIGATORY.

SELECT-OPTIONS: s_budat FOR bkpf-budat OBLIGATORY.

SELECTION-SCREEN END OF BLOCK blk.

*-- initialization

INITIALIZATION.

tit = 'Selection Block'.

*-- at selection screen

AT SELECTION-SCREEN ON s_budat.

CALL FUNCTION 'BAPI_COMPANYCODE_GET_PERIOD'

EXPORTING

companycodeid = p_bukrs

posting_date = s_budat-low

IMPORTING

fiscal_year = w_gjahr1

  • FISCAL_PERIOD =

  • RETURN =

.

CALL FUNCTION 'BAPI_COMPANYCODE_GET_PERIOD'

EXPORTING

companycodeid = p_bukrs

posting_date = s_budat-high

IMPORTING

fiscal_year = w_gjahr2

  • FISCAL_PERIOD =

  • RETURN =

.

IF p_gjahr NE '' AND p_gjahr NE w_gjahr1.

MESSAGE e000 WITH 'Enter valid dates'. "#EC *

ENDIF.

*-- start of selection

START-OF-SELECTION.

*data: burks type p_burks.

SELECT single butxt

FROM t001

INTO v_butxt

WHERE bukrs = p_bukrs.

if sy-subrc = 0.

SELECT lifnr name1

FROM lfa1

INTO CORRESPONDING FIELDS OF TABLE i_lfa1

WHERE lifnr IN s_lifnr.

endif.

*-- getting the opening balances for all vendors

PERFORM openingbalances.

select belnr

gjahr

budat

blart

BLDAT

XBLNR

from bkpf

into corresponding fields of table i_bkpf

where gjahr = p_gjahr and

budat in s_budat and

bukrs = p_bukrs and

belnr in s_belnr and

  • blart = 'KZ' and

bstat = ''.

IF NOT I_BKPF[] IS INITIAL.

SELECT BELNR

koart

shkzg

ktosl

hkont

sgtxt

BUPLA

LIFNR

DMBTR

FROM bseg into corresponding fields of table i_bseg

for all entries in i_bkpf

WHERE belnr = i_bkpf-belnr AND

gjahr = i_bkpf-gjahr AND

bukrs = p_bukrs AND

PRCTR IN S_PRCTR

AND BUPLA IN S_BUPLA

and koart = 'K' AND

lifnr IN s_lifnr.

if sy-subrc <> 0.

message 'value not found' type 'E'.

endif.

ENDIF.

IF NOT I_BSEG[] IS INITIAL.

select BELNR

BUZEI

WT_QBSHH

from with_item

into table i_witem

for all entries in i_bkpf

where belnr = i_bkpf-belnr AND

GJAHR = I_BKPF-GJAHR.

ENDIF.

IF NOT I_BKPF[] IS INITIAL.

select zbukr

vblnr

chect

laufd

from payr

into table i_payr

for all entries in i_bkpf

where zbukr = p_bukrs and

vblnr = i_bkpf-belnr.

ENDIF.

picking GL Account Details

IF NOT I_BSEG[] IS INITIAL.

SELECT saknr

txt20

FROM skat

INTO TABLE i_skat

FOR ALL ENTRIES IN i_bseg

WHERE saknr EQ i_bseg-hkont

AND spras EQ sy-langu

AND ktopl EQ p_bukrs.

ENDIF.

picking Document Details

IF NOT I_BKPF[] IS INITIAL.

SELECT spras

blart

ltext

FROM t003t

INTO TABLE i_t003t

FOR ALL ENTRIES IN i_bkpf

WHERE blart = i_bkpf-blart

AND spras = 'EN'.

ENDIF.

loop at i_bseg .

read table i_bkpf with key belnr = i_bseg-belnr.

itab-bukrs = p_bukrs.

itab-gjahr = i_bkpf-gjahr.

itab-lifnr = i_bseg-lifnr.

itab-bldat = i_bkpf-bldat.

itab-xblnr = i_bkpf-xblnr.

itab-bupla = i_bseg-bupla.

READ TABLE i_witem INTO i_witem WITH KEY belnr = i_bkpf-belnr.

IF sy-subrc IS INITIAL.

itab-wt_qbshh = i_witem-wt_qbshh.

ENDIF.

READ TABLE i_lfa1 WITH KEY lifnr = i_bseg-lifnr.

IF sy-subrc = 0.

itab-name1 = i_lfa1-name1.

itab-opbal = i_lfa1-opbal.

ENDIF.

itab-budat = i_bkpf-budat.

itab-belnr = i_bkpf-belnr.

itab-blart = i_bkpf-blart.

IF i_bseg-shkzg = 'S'.

itab-debit = i_bseg-dmbtr.

itab-credit = 0.

ELSEIF i_bseg-shkzg = 'H'.

itab-credit = i_bseg-dmbtr.

itab-debit = 0.

ENDIF.

itab-sgtxt = i_bseg-sgtxt.

to move GL Account details to final internal table

READ TABLE i_skat INTO i_skat WITH KEY saknr = i_bseg-hkont.

IF sy-subrc IS INITIAL.

  • itab-txt20 = i_skat-txt20.

  • itab-saknr = i_skat-saknr.

MOVE i_skat-txt20 TO itab-txt20. " Bank Name

MOVE i_skat-saknr TO itab-saknr.

ENDIF. "IF sy-subrc IS INITIAL

read table i_t003t into i_t003t with key blart = i_bkpf-blart.

itab-ltext = i_t003t-ltext.

*--to move doc number of the payment doc.

READ TABLE i_payr INTO i_payr WITH KEY vblnr = i_bkpf-belnr.

IF sy-subrc IS INITIAL.

itab-chect = i_payr-chect.

itab-laufd = i_payr-laufd.

ENDIF.

APPEND itab.

CLEAR itab.

  • ENDSELECT.

ENDLOOP.

LOOP AT i_lfa1.

READ TABLE itab WITH KEY lifnr = i_lfa1-lifnr.

IF sy-subrc NE 0.

itab-bukrs = p_bukrs.

itab-gjahr = p_gjahr.

itab-lifnr = i_lfa1-lifnr.

itab-name1 = i_lfa1-name1.

itab-opbal = i_lfa1-opbal.

APPEND itab.

CLEAR itab.

ENDIF.

ENDLOOP.

sort itab by bukrs gjahr lifnr budat.

LOOP AT itab.

w_name1 = itab-name1.

w_opbal = itab-opbal.

w_cbal = w_opbal * -1.

itab-debit = itab-debit + itab-wt_qbshh.

w_ctot = w_ctot + itab-credit.

w_dtot = w_dtot + itab-debit.

  • itab-balance = itab-credit.

if w_dtot is not initial.

itab-balance = itab-balance + w_dtot.

endif.

if w_ctot is not initial.

itab-balance = itab-balance - w_ctot.

endif.

clear itab-balance.

AT new lifnr.

  • on change of s_lifnr.

w_cfbal = w_opbal.

FORMAT COLOR COL_NORMAL INTENSIFIED ON.

IF w_opbal GE 0.

WRITE:/01 sy-vline,

02 'Vendor:',

12(10) itab-lifnr,

26 w_name1,

  • 177 sy-vline.

175 sy-vLINE.

WRITE:/01 sy-vline,

02 'Opening Balance:',

131(13) w_opbal, "#EC *

  • 177 sy-vline.

175 sy-vLINE.

ELSEIF w_opbal LT 0.

WRITE:/01 sy-vline,

02 'Vendor:',

12(10) itab-lifnr,

26 w_name1,

  • 177 sy-vline.

175 sy-vline.

WRITE:/01 sy-vline,

02 'Opening Balance:',

148(13) w_opbal, "#EC *

  • 177 sy-vline.

175 sy-vline.

ENDIF.

FORMAT COLOR OFF.

ENDAT.

  • total = itab-balance .

if w_ctot ge 0 and w_dtot ge 0.

itab-balance = w_opbal + w_dtot - w_ctot.

  • itab-balance = itab-balance - w_ctot.

endif.

if w_ctot is not initial and w_dtot le 0.

itab-balance = w_opbal - w_dtot - w_ctot.

endif.

FORMAT COLOR COL_NORMAL INTENSIFIED OFF.

WRITE:/01 sy-vline,

02(10) itab-belnr CENTERED,

15 sy-vline,

16(10) itab-budat CENTERED,

27 sy-vline,

28(18) itab-ltext LEFT-JUSTIFIED,

45 sy-vline,

  • 47(20) itab-txt20 CENTERED,

  • 70 sy-vline,

46(9) itab-bupla centered,

55 sy-vline,

56(50) itab-sgtxt LEFT-JUSTIFIED,

115 sy-vline,

116(15) itab-xblnr centered,

130 sy-vline,

131(16) itab-debit centered,

145 sy-vline,

146(16) itab-credit centered,

160 sy-vline,

161(13) itab-balance left-JUSTIFIED,

175 sy-vline.

  • 164(14) itab-balance left-justified,

  • 177 sy-vline.

FORMAT COLOR OFF.

AT END OF lifnr.

IF w_opbal LT 0.

w_ctot = w_ctot + w_cbal .

ELSEIF w_opbal GE 0.

w_dtot = w_dtot + w_opbal .

ENDIF.

CLEAR w_cbal.

CLEAR w_opbal.

CLEAR w_name1.

CLEAR w_ftotal.

CLEAR w_cftotal.

  • clear itab-balance.

SUM.

  • ULINE (177).

ULINE (175).

if itab-credit ge 0 and w_dtot ge 0.

w_ftotal = w_cfbal + w_dtot - itab-credit.

endif.

if itab-credit is not initial and w_dtot le 0.

w_ftotal = w_cfbal - w_dtot - itab-credit.

endif.

FORMAT COLOR COL_TOTAL ON.

WRITE:/01 sy-vline,

02 'Total:',

130(13) w_dtot right-justified, "#EC *

143(16) w_ctot, "#EC *

159(16) w_ftotal,

  • 177 sy-vline.

175 sy-vline.

ULINE.

CLEAR: w_ctot,w_dtot.

w_ftotal = w_cfbal + itab-debit - itab-credit.

w_cftotal = w_ftotal * -1.

clear w_ctot.

clear w_dtot.

ENDAT.

ENDLOOP.

*-- top of page

TOP-OF-PAGE.

WRITE:/48 v_butxt. " COLOR COL_GROUP.

WRITE:/45 '----


'.

WRITE:/48 'Creditors Ledger'. " COLOR COL_KEY.

WRITE:/45 '----


'.

SKIP 1.

WRITE:/2 'Company Code:',16 p_bukrs,

108 'Page No :', sy-pagno.

WRITE:/2 'Fiscal Year :',16 p_gjahr.

WRITE:/2 'Period :', s_budat-low,'to', s_budat-high.

  • ULINE (177).

ULINE (175).

FORMAT COLOR COL_HEADING ON.

WRITE:/01 sy-vline,

02(12) 'Document No' CENTERED,

15 sy-vline,

16(10) 'PDate' CENTERED,

27 sy-vline,

28(20) 'Voucher Type' LEFT-JUSTIFIED,

45 sy-vline,

46(9) 'Bis Place' CENTERED,

  • 70 sy-vline,

55 sy-vline,

56(12) 'Narration' CENTERED,

115 sy-vline,

116(12) 'Cheque / Bill No' CENTERED,

130 sy-vline,

131(16) 'Debit' CENTERED,

145 sy-vline,

146(16) 'Credit' CENTERED,

160 sy-vline,

161(10) 'Balance' CENTERED,

175 sy-vline.

  • 164(14) 'Balance' left-justified,

  • 177 sy-vline.

***endcode

FORMAT COLOR OFF.

  • ULINE (177).

ULINE (15).

&----


*& Form openingbalances

&----


  • text

----


  • --> p1 text

  • <-- p2 text

----


FORM openingbalances.

w_pdate = s_budat-low - 1.

LOOP AT i_lfa1.

CALL FUNCTION 'BAPI_AP_ACC_GETKEYDATEBALANCE'

EXPORTING

companycode = p_bukrs

vendor = i_lfa1-lifnr

keydate = w_pdate

balancespgli = 'X'

  • NOTEDITEMS = ' '

  • IMPORTING

  • RETURN =

TABLES

keybalance = itab_key_bal

.

REFRESH ibal.

CLEAR ibal.

CLEAR w_sbal.

APPEND LINES OF itab_key_bal TO ibal.

REFRESH itab_key_bal.

CLEAR itab_key_bal.

LOOP AT ibal.

  • ibal-lc_bal = ibal-lc_bal / 100.

w_sbal = w_sbal + ibal-lc_bal.

ENDLOOP.

i_lfa1-opbal = w_sbal.

MODIFY i_lfa1.

CLEAR i_lfa1.

REFRESH ibal.

CLEAR ibal.

CLEAR w_sbal.

ENDLOOP.

ENDFORM. " openingbalances

1 REPLY 1

Former Member
0 Kudos

Hi,

Instead of using BSEG use BSIK, which contains all open items. Use BUDAT to get all open records as of a particular date. You have already got the o/p balance, so add/Sub ( depending on BSAK-SHKZG = 'S' or 'H' )amounts from all records selected ffrom BSIK for a particular Vendor to arrive at the closiong balance.

I hope this helps,

Regards

Raju chitale