repots( closing balance of a vendor)
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